Whenever I need to create an SP, this is the file I start with.
A quick find and replace of SP_NAME, the deletion of the transaction code if it's not needed and the entering of the main SQL statements in place of MAIN_STATEMENT is all it takes to end up with a file with which to create a stored procedure from.
IF EXISTS (SELECT * FROM sysobjects WHERE name = "SP_NAME" AND type = "P") BEGIN DROP PROCEDURE SP_NAME PRINT "Dropped procedure: SP_NAME" END GO CREATE PROCEDURE SP_NAME PARAMS AS BEGIN BEGIN TRANSACTION SET NOCOUNT ON DECLARE @errorCode INT MAIN_STATEMENT SELECT @errorCode = @@ERROR --EXEC @errorCode = ANOTHER_SP @PARAM1, @PARAM2 IF @errorCode <> 0 GOTO ERROR_EXIT GOTO PROCEDURE_EXIT ERROR_EXIT: IF @@TRANCOUNT <> 0 ROLLBACK TRANSACTION RETURN @errorCode PROCEDURE_EXIT: IF @@TRANCOUNT <> 0 COMMIT TRANSACTION RETURN @errorCode END GO IF EXISTS (SELECT * FROM sysobjects WHERE name = "SP_NAME" AND type = "P") BEGIN PRINT "Created procedure: SP_NAME" -- GRANT EXECUTE ON SP_NAME TO USER END ELSE BEGIN PRINT "Error creating procedure: SP_NAME" END GO
The following might warrant an explaination:
MAIN_STATEMENT SELECT @errorCode = @@ERROR --EXEC @errorCode = ANOTHER_SP @param IF @errorCode <> 0 GOTO ERROR_EXIT
After a statement is run, we select the error code into our own local variable, @errorCode, to be checked on later. If we don't do this, but instead check @@ERROR directly, the IF statement alters @@ERROR!
The commented out line is a call to another SP which also selects the error code it produces, into a local variable.