Wednesday, 16 March 2011

SQL Stored procedure with error handling using Try/Catch

This is improved version of the script from 2009 using try/catch syntax

USE [MyDatabase]
GO
PRINT 'DROP PROCEDURE [usp_mysproc]'
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_mysproc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[usp_mysproc]
GO
PRINT 'CREATE PROCEDURE [usp_mysproc]'
GO
CREATE PROCEDURE [dbo].[usp_mysproc]
(
  @RaiseError bit = 0
)  
AS
BEGIN TRY
  BEGIN TRAN
  
  IF @RaiseError = 1
  BEGIN
    DECLARE @TEMP TABLE (Column1 varchar(2))
    INSERT INTO @TEMP(Column1) VALUES ('123')    
  END
  
  COMMIT TRAN
END TRY
BEGIN CATCH
  DECLARE @ErrorInfo VARCHAR(4000)      
  SET @ErrorInfo = 'ERROR ['+LTRIM(STR(ERROR_NUMBER()))+']['+ERROR_PROCEDURE()+':'+LTRIM(STR(ERROR_LINE()))+'] - ' + ERROR_MESSAGE() 
  
  PRINT CONVERT(VARCHAR,GETDATE(),21) + ': '+@ErrorInfo
  ROLLBACK TRAN 
  RAISERROR( @ErrorInfo, 16, 1)
  RETURN 1
END CATCH    
GO
GRANT  EXECUTE  ON [dbo].[usp_mysproc]  TO [MyDatabaseEditor]
GO
GRANT  EXECUTE  ON [dbo].[usp_mysproc]  TO [MyDatabaseAdmin]
GO
-- SUCCESS TEST
EXEC usp_mysproc
-- FAILURE TEST
EXEC usp_mysproc @RaiseError = 1
-- ERROR MESSAGE 
-- 2011-03-16 12:23:12.893: ERROR [8152][usp_mysproc:14] - String or binary data would be truncated.

No comments:

Post a Comment