DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to catch duplicate insert with TRY-CATCH?

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

*/

------------

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE