|
Execute the following SQL Server T-SQL example scripts in Management Studio Query Editor to create and test a stored procedure with duplicate insert try-catch.
-- SQL duplicate insert error handling - SQL Server try - catch
-- SQL stored procedure - SQL duplicate error logging
USE pubs;
GO
CREATE TABLE ErrorLogForTransactions
(UserName sysname,
TableName sysname,
ErrorNumber sysname,
ErrorSeverity sysname,
ErrorState sysname,
ErrorMessage nvarchar(1024),
ModifiedDate datetime default (getdate()))
GO
CREATE PROC procInsertNewBookTitle(
@TitleID VARCHAR(6),
@NewTitle NVARCHAR(128),
@Price MONEY)
AS
DECLARE @TableName SYSNAME,
@ErrorMessage NVARCHAR(1024)
BEGIN TRY
BEGIN TRANSACTION
SELECT @ErrorMessage = 'Duplicate insert failed',
@TableName = 'Titles'
INSERT dbo.Titles
(Title_id, Title, Price)
VALUES(@TitleID, @NewTitle,@Price)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- Log the error into transaction error logging table
INSERT dbo.ErrorLogForTransactions
(UserName,
TableName,
ErrorNumber,
errorSeverity,
errorState,
ErrorMessage)
VALUES(suser_sname(),@TableName,ERROR_NUMBER(),ERROR_SEVERITY(),
ERROR_STATE(),ERROR_MESSAGE())
-- T-SQL raiserror to notify application about error
RAISERROR (@ErrorMessage,16,1)
END CATCH
GO
-- Test stored procedure
-- Execute stored procedure
-- Select * from titles
SELECT TOP 1 title_id from titles -- get test data for duplicate test
-- PC1035
EXEC procInsertNewBookTitle 'PC1035', 'Is Your Webpage User Friendly?', 20.0
GO
/* Messages
(0 row(s) affected)
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure procInsertNewBookTitle, Line 36
Duplicate insert failed
*/
SELECT * FROM ErrorLogForTransactions
GO
/* Results
UserName TableName ErrorNumber ErrorSeverity ErrorState ErrorMessage ModifiedDate
DELLSTAR\Mary Smith Titles 2627 14 1 Violation of PRIMARY KEY constraint 'UPKCL_titleidind'. Cannot insert duplicate key in object 'dbo.titles'. 2009-02-21 04:06:29.047
*/
------------ |
