USE [colombo]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[sp_tmproc]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_tmproc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==============================================================
Author: [ amper ]
Create date: [ 2007-06-25 ]
Description: [demo procedure for trapping errors]
================================================================ */
CREATE PROCEDURE dbo.sp_tmproc @empno INT, @fact NVarchar(200)
AS
BEGIN
BEGIN TRY
/*
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.
*/
INSERT INTO EmployeeFacts (EmpNo,fact,createdate)
VALUES (@empno, @fact, GetDate());
END TRY
BEGIN CATCH
/*
ERROR_NUMBER()=2601: Cannot insert duplicate key row in object
'EmployeeFacts' with unique index 'idx1_empfact_unique'
*/
IF ( ERROR_NUMBER()=2601 )
BEGIN
/* create table tmp_EmpFacts if table does not exists */
IF NOT EXISTS
(SELECT * FROM sys.objects
WHERE object_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
ELSE
BEGIN
/* in all other cases error code and message will be displayed */
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END
END CATCH
END
GO
GRANT EXECUTE ON OBJECT::[dbo].[sp_tmproc] TO george;
GO
|