Determine the value / column that caused the restriction violation in TSQL?

Question

DECLARE @temp AS TABLE (id INT NOT NULL PRIMARY KEY CLUSTERED, name VARCHAR(10)) BEGIN TRY declare @Source table (id int not null, name varchar(10)) insert @Source SELECT 11,'ABC' union all SELECT 12,'CDE' union all SELECT 13,'FGH' union all SELECT 11,'IJK' INSERT INTO @temp SELECT * from @Source SELECT * FROM @temp END TRY BEGIN CATCH EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile Name', @recipients = ' [e-mail] ', @body = Error_Message, @subject = Error_Message ; END CATCH; GO 

This is my code that works fine .. But what I want to include in the mail is an identifier that causes an error as a reminder of some email id in this case, it is "11". How can I do this in a reasonable way.?

Answer 1

As Martin commented, a duplicate key value is displayed in 2008 SP3.
For earlier versions, you will need to detect a possible violation of the primary key and raise the error yourself. for your specific example

 DECLARE @temp AS TABLE (id INT NOT NULL PRIMARY KEY CLUSTERED, name VARCHAR(10)) BEGIN TRY declare @Source table (id int not null, name varchar(10)) insert @Source SELECT 11,'ABC' union all SELECT 12,'CDE' union all SELECT 13,'FGH' union all SELECT 11,'IJK' declare @duplicate_key nvarchar(1000); SELECT TOP(1) @duplicate_key = ID FROM @Source GROUP BY ID HAVING COUNT(*) > 1; if @duplicate_key is not null begin set @duplicate_key = 'Violation of PRIMARY KEY constraint. '+ 'Cannot insert duplicate key in object '' dbo.@temp ''. '+ 'The duplicate key value is (' + right(@duplicate_key,10) + ')'; RAISERROR(@duplicate_key, 16, 1); end; INSERT INTO @temp SELECT * from @Source SELECT * FROM @temp END TRY BEGIN CATCH select Error_Message() ; END CATCH; GO