Hi,
let me share this SQL block, which seems to be useful for proper error handling in stored procedures...
UPDATED: I found a defect in the original error handler (message was limited to 300 chars). Below is the refined version:
BEGIN TRY
BEGIN TRAN usp_procXXX
$CURSOR$--
COMMIT TRAN usp_procXXX
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage varchar(max)
, @ErrorSeverity int
, @ErrorState int
SELECT @ErrorMessage =
'Error '
+ CAST( ERROR_NUMBER() as varchar )
+ ' "' + ERROR_MESSAGE() + '" '
+ 'raised in ' + ISNULL( ERROR_PROCEDURE(), 'raw SQL' )
+ ' line ' + CAST( ERROR_LINE() as varchar )
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
IF XACT_STATE() <> 0
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION usp_procXXX;
END
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState )
END CATCH
let me share this SQL block, which seems to be useful for proper error handling in stored procedures...
UPDATED: I found a defect in the original error handler (message was limited to 300 chars). Below is the refined version:
BEGIN TRY BEGIN TRAN usp_procXXX $CURSOR$-- COMMIT TRAN usp_procXXX END TRY BEGIN CATCH DECLARE @ErrorMessage varchar(max) , @ErrorSeverity int , @ErrorState int SELECT @ErrorMessage = 'Error ' + CAST( ERROR_NUMBER() as varchar ) + ' "' + ERROR_MESSAGE() + '" ' + 'raised in ' + ISNULL( ERROR_PROCEDURE(), 'raw SQL' ) + ' line ' + CAST( ERROR_LINE() as varchar ) , @ErrorSeverity = ERROR_SEVERITY() , @ErrorState = ERROR_STATE() IF XACT_STATE() <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION usp_procXXX; END RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState ) END CATCH