I am using SP_EXECSQL to execute a select and potential insert into a dynamically named table if the value does not exist. I am trying to catch / handle key violation errors and re-raise all other in an effort to terminate the batch and inform the
caller of the error. Unfortunately, the batch code after SP_EXECUTESQL continues to execute in the event of non 2601 errors. Here is the relevant block inside of my sp:
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @WID = WordID FROM ' + @WTable + ' WHERE Word = @Word
IF @WID IS NULL
BEGIN TRY
INSERT INTO ' + @WTable + ' (Word) VALUES (@Word)
SET @WID = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE();
IF ERROR_NUMBER() = 2601 --key violation, two threads tried to insert the same word at the same time
SELECT @WID = WordID FROM ' + @WTable + ' WHERE Word = @Word
ELSE
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) --some other error, raise it back to client
END CATCH
'
SET @ParamDefs = N'@Word nvarchar(50), @WID bigint OUTPUT'
EXEC SP_EXECUTESQL @SQL, @ParamDefs, @Word, @WID OUTPUT
--Batch execution continues on here even though the catch block raises an
View Complete Post