USE [colombo]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects
where id = object_id(N'[dbo].[sp_tmproc]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_tmproc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==================================================================
Author: [ amper ]
Create date: [ 2006-10-17 ]
Description: [demo procedure for trapping errors. version for
sqlserver 2000]
==================================================================== */
CREATE PROCEDURE dbo.sp_tmproc @empno INT, @fact NVarchar(200)
AS
BEGIN
DECLARE @ErrNo INT
DECLARE @SQLString NVarchar(500), @ErrStr NVarchar(250)
SET @ErrNo = 0
/*
Try to insert record into table EmployeeFacts. The table has unique
index 'idx1_empfact_unique' on columns EmpNo, fact. Duplicate
values for the same employee and fact will not be excepted.
*/
SET @SQLString =
'INSERT INTO EmployeeFacts (EmpNo,fact,createdate) ' +
' VALUES (' + CAST(@empno AS NVarchar(20)) + ',''' +
@fact + ''', GetDate())'
EXEC @ErrNo = sp_executesql @SQLString
/*
If sp_executesql procedure fails for some reason, it returns Error
Code different from 0. We can recive back Error Code 2601 that
means: Cannot insert duplicate key row in object 'EmployeeFacts'
with unique index 'idx1_empfact_unique'
*/
SET @ErrStr = '[1] ' + CAST(@ErrNo AS NVarchar(20))
IF ( @ErrStr=2601 )
BEGIN
/* create table tmp_EmpFacts if table does not exists */
IF NOT EXISTS
(SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[tmp_EmpFacts]')
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tmp_EmpFacts](
[EmpNo] [int] NULL,
[fact] [nvarchar](200) NULL,
[createdate] [datetime] NULL) ON [PRIMARY];
END
/*
table tmp_EmpFacts stores records rejected from being inserted
into EmployeeFacts table
*/
INSERT INTO tmp_EmpFacts (EmpNo,fact,createdate)
VALUES (@empno, @fact, GetDate());
END
SET @ErrStr = @ErrStr + ' # [2] ' +CAST(@@ERROR AS NVarchar(20))
/* show log string of concatenated errors */
SELECT @ErrStr AS ErrLog
END
GO
GRANT EXECUTE ON OBJECT::[dbo].[sp_tmproc] TO george;
GO
|