Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



Stored Procedure Template - SQL Server

Modified on Sat, Oct 31, 2009, 10:27 PM by Administrator Categorized as SQL Server
The following is a template for creating a stored procedure in SQL Server

create procedure dbo.MyProcedure
	@InputParameter varchar(256)
	) as 

begin try

    begin transaction

    -- Your code goes here.

    commit transaction

end try
begin catch

    rollback transaction

        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR (@ErrorMessage, @ErrorSeverity, 1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.

end catch

ScrewTurn Wiki version Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.