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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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