PRINT 'Create procedure MySProc'
GO
EXEC dbo.MySProcToLogCreateOfAnSProc 'MySProc', '050715'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProc]')
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE dbo.MySProc
GO

CREATE PROCEDURE dbo.MySProc
The first parameter to ALL our SProcs is a SessionID
which is handy for logging errors, and identifing who/what/when
	@MySessionID	int,

... actual parameters to the procedure next ...

	@intSilent	int=0,
1=Silent on all ERROR resultsets.  We use this when we call this SP
from another SP, rather than an ASP page.  It prevents errors being output
as, for example, record sets - instead passing them back as return values

	@intDebug	int=0
0=No debugging data, 1+ = debugging at increasing levels of details
AS
/*
... Descriptive header, change history, etc. ...
... Also a list of Error Numbers within this SProc - 1=Customer insert error, 2=...
*/
Standard header stuff here, same for all Sprocs
SET NOCOUNT ON		
SET XACT_ABORT ON
SET ARITHABORT ON
-- System variables
DECLARE	@intErrNo	int,		-- Working storage for @@ERROR
	@strErrMsg	varchar(255),	-- Human Readable!! error message
	@intRetVal	int,		-- Return value, 0=No error
	@dtNow		datetime,	-- DateTime of start of execution
	@intRowCount	int		-- Working storage for @@ROWCOUNT

	SELECT	@dtNow = GetDate(),
		@intErrNo = 0,	-- Assume no error
		@strErrMsg = '',
		@intRetVal = 0	-- Return value (Assume no error)

Then the "Guts" of the SProc.  For example a SELECT then an INSERT

	IF @intDebug >= 1 SELECT [MySProc DEBUG(1)] = 'SELECT', [@Param1]=@Param1
	SELECT	ColA, ColB, ColC
	FROM	dbo.MyTable
IMMEDIATELY store system values into working variables
	SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

Check for Error / inappropriate rowcount
	IF @intErrNo <> 0 OR @intRowCount < 2
	BEGIN
"Assign" a Return Code and a Human Readable message (the message is cumulative)
		SELECT	@intRetVal = -1,
			@strErrMsg = @strErrMsg + 'Error in the SELECT bit.  '
Depending on the severity we may have a GOTO MySProc_EXIT here
	END

Where we have some validation stuff and then some Update Stuff
we SKIP the update stuff if we already have an error
	IF @intRetVal <> 0 GOTO MySProc_EXIT

	BEGIN TRANSACTION MySproc_01
	SAVE  TRANSACTION MySproc_02

	IF @intDebug >= 1 SELECT [MySProc DEBUG(2)] = 'INSERT', [@ParamX]=@ParamX
	INSERT MyTable (ColA, ColB, ColC)
        VALUES (@Param1, @Param2, @Param3)
	SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

	IF @intErrNo <> 0 OR @intRowCount <> 1
	BEGIN
		SELECT	@intRetVal = -2,
			@strErrMsg = @strErrMsg + 'Got an error in the INSERT bit.  '
We use the ABORT label at this point, rather than the EXIT one,
to force a rollback
		GOTO MySProc_ABORT
	END

MySProc_ABORT:
Arrive here if we were aborting WITHIN a transaction block
	IF @intRetVal = 0
	BEGIN
		COMMIT TRANSACTION MySProc_01
	END
	ELSE
	BEGIN
		ROLLBACK TRANSACTION MySProc_02
		COMMIT TRANSACTION MySProc_01
		IF @intDebug >= 1 
		BEGIN
			SELECT [MySProc DEBUG]='ROLLBACK',
				[@intRetVal]=@intRetVal
		END
	END

MySProc_EXIT:
Arrive here if we were aborting OUTSIDE then transaction block
	IF (@intRetVal <> 0 AND @intSilent=0) OR @intDebug >= 1
	BEGIN
		SELECT	[ERROR] = @strErrMsg, [RetVal] = @intRetVal,
			[ErrNo] = @intErrNo,
			[SP] = 'MySproc',
			[Params1] = @Param1
			....
	END

	SET NOCOUNT OFF
The ReturnCode would be better as an OUTPUT parameter, but I didn't know that at the time
	RETURN @intRetVal	-- Return error number, 0=No error
/* TEST RIG

EXEC dbo.MySproc 123, 'SomeParameter'
	,@intDebug=1

 */
--================== MySProc ==================--
GO

IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProc]')
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	GRANT EXECUTE ON dbo.MySProc TO MyRole
GO
PRINT 'Create procedure MySProc DONE'
GO