SQL

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.