Friday, 30 January 2009

SQL Create stored procedure script with error handling

Click here to see improved version of this script 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]
AS
BEGIN TRAN
DECLARE @errorNo int, @rowCount int, @errorStr varchar(4000)
DELETE FROM Table1 Where SampleDate < getdate()
SELECT @errorNo = @@ERROR, @rowCount = @@ROWCOUNT
IF @errorNo <> 0 BEGIN
    SET @errorStr = 'Error deleting old load records "load_Master"'
    GOTO ErrHandler
END
COMMIT
RETURN 0
ErrHandler:
    ROLLBACK
    DECLARE @procName sysname
    SELECT @procName = object_name( @@procid ), @errorStr = ISNULL( @errorStr, 'Unknown error' )
    IF @errorNo <> 0 BEGIN
        SELECT @errorStr = @errorStr + ': procedure=%s, error=%i'
        RAISERROR( @errorStr, 16, 1, @procName, @errorNo )
    END
    ELSE BEGIN
        SELECT @errorStr = @errorStr + ': procedure=%s'
        RAISERROR( @errorStr, 16, 1, @procName )
    END
    RETURN 1
 
GO
GRANT  EXECUTE  ON [dbo].[usp_mysproc]  TO [MyDatabaseEditor]
GO
GRANT  EXECUTE  ON [dbo].[usp_mysproc]  TO [MyDatabaseAdmin]
GO

No comments:

Post a Comment