SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_test_error]

AS
BEGIN

DECLARE @intErrorCode INT
DECLARE @source varchar(50)


BEGIN TRAN
	SET @source = 'sp_someProc1'
    EXEC dbo.sp_someProc1

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0)	GOTO PROBLEM

	SET @source = 'sp_someProc2'
	EXEC dbo.sp_someProc2

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
    
COMMIT TRAN

Return 0

PROBLEM:

Insert into ERROR_LOG (ERROR_DATE, ERROR_USER, [ERROR_MESSAGE], ERROR_SOURCE, [ERROR_NUMBER], ERROR_HOST)
Select getdate(), SYSTEM_USER, '',@source, @intErrorCode, HOST_NAME()

    ROLLBACK TRAN
    
Return 1

END

USE [myActions]
GO
/****** Object:  StoredProcedure [dbo].[isp_LOG_SprocExecLog]    Script Date: 04/30/2010 14:37:34 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[isp_LOG_SprocExecLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[isp_LOG_SprocExecLog]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[isp_LOG_SprocExecLog]
	 @SprocName			nvarchar(256)
	,@TranStart			datetime
	,@TranEnd			datetime
	,@ReturnCode		int
	,@ErrorCode			int
	,@ErrorMessage		nvarchar(4000) = null
	,@Rows				int
	,@App_User			nchar(30)
	,@Parameters		varchar(max)
	,@RC				int OUTPUT
AS


--
--                 		ATE Technology Services
--
--           File:      \\paerscns20\ss\GBTS_Systems_Projects\Projects\myActions\Database\SQLServer\NJROS1BBLD0304\DEV2K08\Sprocs
--           Date:      2011-07-08
--         Author:      Brett Kaiser
--         Server:      paerscbvd0403\dev
--       Database:      myActions
--          Login:      sa
--    Description:      This Procedure will log all procedures executed in a database.
--
--
--                      The stream will do the following:
--
--    '1. Function...
--
--
--    Tables Used: 		[SprocExecLog]
--
-- Tables Created:      None
--
--
--  Row Estimates:
-- name              rows        reserved           data               index_size             unused
-- --------------------------------------------------------------------------------------------------
-- [SprocExecLog]       0            0 KB           0 KB                     0 KB               0 KB
--
-- sp_spaceused [SprocExecLog]
--
--Change Log
--
-- UserId    	Date         	Description
-- -----------  --------------  ------------------------------------------------------------------------------------------
-- x002548   	2011/07/08  	1. Initial release

/*
Sample Execution:


DECLARE @RC int
EXEC [dbo].[isp_LOG_SprocExecLog]
	  'TestSproc'							--@SprocName		nvarchar(256)
	, '2010-04-16 13:19:39.620'				--@TranStart		datetime
	, '2010-04-16 13:19:39.620'				--@TranEnd			datetime
	, 99									--@ReturnCode		int
	, 99									--@ErrorCode		int
	, 'No error, just a test.'				--@ErrorMessage		nvarchar(4000)
	, 99									--@Rows				int
	, 'X036992'								--@App_User			nchar(7)
	, 'Test SprocLog Execution Log Load'	--@Parameters		varchar(max)
	, @RC						OUTPUT		--@rc			int OUTPUT
SELECT @RC AS [RC]

SELECT TOP 10 * FROM [SprocExecLog] 
--WHERE SprocName = 'TestSproc' 
ORDER BY TranStart DESC

*/

DECLARE @RowCount int, @Error_Loc int, @Error_Type int, @Error_Message varchar(255), @Error int
DECLARE @s datetime, @Log_ID int
 SELECT @rc = 0, @s = GETDATE()

BEGIN TRAN

   INSERT INTO [dbo].[SprocExecLog] (
          [SprocName]
         ,[TranStart]
         ,[TranEnd]
         ,[LogStart]
         ,[LogEnd]
         ,[ReturnCode]
         ,[ErrorCode]
         ,[ErrorMessage]     
         ,[Rows]
         ,[App_User]
         ,[Parameters]
								)
   SELECT 
          @SprocName
         ,@TranStart
         ,@TranEnd
         ,@s
         ,GetDate()
         ,@ReturnCode
         ,@ErrorCode
         ,@ErrorMessage     
         ,@Rows
         ,@App_User
         ,@Parameters

     SELECT @ErrorCode = @@ERROR, @Rowcount = @@ROWCOUNT, @Log_Id = SCOPE_IDENTITY()

	 IF @ErrorCode <> 0
	   BEGIN
	     SELECT @Error_Loc = 2, @Error_Type = 50001, @rc = -6662
	     GOTO isp_LOG_SprocExecLog_Error
	   END
	
	 IF @RowCount <> 1
	  BEGIN 
	   SELECT @Error_Loc = 3
	        , @Error_Message =  'Expected 1 row to be inserted in to the SprocExecLog.  Actual Number inserted = '
			+ CONVERT(varchar(5),@RowCount)
	        , @Error_Type = 50002, @rc = -6663
	   GOTO isp_LOG_SprocExecLog_Error
	  END


COMMIT TRAN


isp_LOG_SprocExecLog_Exit:

--UPDATE [SprocExecLog] SET [LogEnd] = GetDate()
 --WHERE [Log_ID] = @Log_ID

  SET NOCOUNT OFF
  RETURN 

isp_LOG_SprocExecLog_Error:
  ROLLBACK TRAN

  IF @Error_Type = 50001
    BEGIN
      SELECT @error_message = (SELECT 'Location: ' + ',"' + RTRIM(CONVERT(char(3),@Error_Loc))  
            + ',"' + '  @@ERROR: ' + ',"' + RTRIM(CONVERT(char(6),error))
            + ',"' + ' Severity: ' + ',"' + RTRIM(CONVERT(char(3),severity))
--          + ',"' + '  Message: ' + ',"' + RTRIM(description)
        FROM master..sysmessages
       WHERE error = @error)
    END

  IF @Error_Type = 50002
    BEGIN
      SELECT @Error_Message = 'Location: ' + ',"' + RTRIM(CONVERT(char(3),@Error_Loc)) 
            + ',"' + ' Severity:  UserLevel ' 
            + ',"' + ' Message: ' + ',"' + RTRIM(@Error_Message)
    END

  RAISERROR @Error_Type @Error_Message
  GOTO isp_LOG_SprocExecLog_Exit
GO

GRANT VIEW DEFINITION                ON [dbo].[isp_LOG_SprocExecLog]           TO [myActions_User99_Role]
GRANT EXECUTE                        ON [dbo].[isp_LOG_SprocExecLog]           TO [myActions_User99_Role]
GO

CREATE PROC [dbo].[usp_DEL_Category]
	  @Category_Cd                              decimal(15,3)                   -- Category
	, @User										char(12)	
	, @rc int OUTPUT, @Error int OUTPUT,  @Error_Message varchar(255) OUTPUT, @Rowcount int OUTPUT
AS 

USE [myActions]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DEL_Category]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_DEL_Category]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_DEL_Category]
	  @Category_Cd                              decimal(15,3)                   -- Category
	, @User										char(12)	
	, @rc int OUTPUT, @Error int OUTPUT,  @Error_Message varchar(255) OUTPUT, @Rowcount int OUTPUT
AS 


--
--                 Enterprise Solutions
--
--           File:      \\paerscns20\ss\GBTS_Systems_Projects\Projects\myActions\Database\SQLServer\NJROS1BBLD0304\DEV2K08\Sprocs
--           Date:      09/29/2011
--         Author:      Sarah Abdallah
--         Server:      NJROS1BBLD0304\DEV2K08
--       Database:      myActions
--          Login:      myActions_User99
--		Procedure:		usp_DEL_Category
--    Description:      Delete a Row in the Category table
--
--   Return codes:		-1	Unsuccessful Execution 
--						0	Successful Execution
--						1	Zero rows returned or modified
--
--    Tables Used:		Category
--
-- Tables Created:		None
--
--
--  Row Estimates:
--	name						rows        reserved           data               index_size         unused
--	--------------------		----------- ------------------ ------------------ ------------------ ------------------
--	Message_Code				3           16 KB               8 KB               8 KB               0 KB
--
-- sp_spaceused Category
--
--Change Log
--
-- UserId    	Date         	Description
-- -----------  --------------  ------------------------------------------------------------------------------------------
-- X163043		09/29/2011		Initial Release
--




/* Sample Execution:

--SELECT * FROM Category

--Insert test row to be deleted first.

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int; 
EXEC usp_INS_Category
  @Category_Cd			=6.6
, @Category_Name		='XXX'
, @Category_Desc		='XXX'
, @User					='X002548'
, @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT; 
SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]


-- Test Delete

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int; 
EXEC usp_DEL_Category
  @Category_Cd			=6.6
, @User					='X002548'
, @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT; 
SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]


-- Test Delete of a nonexisting row

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int; 
EXEC usp_DEL_Category
  @Category_Cd			=5.0
, @User					='X002548'
, @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT; 
SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]

SELECT * FROM Category

SELECT		 [Host_Name]
			, SYS_USER
			, SPROCNAME
			, Transtart
			, DATEDIFF(ms,Transtart,TranEnd) AS TransLength
			, ReturnCode, Rows, ErrorCode, ErrorMessage
, *
FROM SprocExecLog
WHERE SprocName = 'usp_DEL_Category'
ORDER BY 4 DESC


  SELECT TOP 1 Parameters 
    FROM SprocExecLog
  WHERE SprocName = 'usp_DEL_Category'
ORDER BY TranStart DESC

*/


SET NOCOUNT ON

BEGIN TRAN

DECLARE @s datetime, @e datetime, @Error_Type int, @Error_Loc int, @sprc int, @rowcount2 int, @Parameters varchar(MAX), @Sprocname varchar(255)

SELECT @rc = 0, @s = GetDate(), @Error = 0, @Error_Message='', @Rowcount = 0, @Rowcount2 = 0, @Sprocname = object_name(@@procid)

 DELETE FROM	   Category
		WHERE	   Category_Cd                             = @Category_Cd  

SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT 

IF @Error <> 0 
  BEGIN 
	SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1 
	GOTO usp_DEL_Category_Err 
  END

IF @Rowcount = 0 
  BEGIN 
	SELECT @Error_Loc = 2, @Error_Type = 50002, @rc = 1 
   	     , @Error_Message = 'No Rows Deleted for '
   			+ 'Category_Cd = '			+ '''' + CONVERT(varchar(15),@Category_Cd) + ''''
   	GOTO usp_DEL_Category_Err 
  END

SELECT @Rowcount2= @Rowcount2 + @Rowcount

SELECT @Rowcount = @Rowcount2

COMMIT TRAN

usp_DEL_Category_Exit:
  SET @e = GetDate()
  SET @Parameters = 'DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int; '+CHAR(13)+CHAR(10)
	+ 'EXEC ' + @SprocName +CHAR(13)+CHAR(10)
	+ '  @Category_Cd='		+COALESCE(         RTRIM(@Category_Cd)				 ,'NULL')+CHAR(13)+CHAR(10)
    + ', @User='			+COALESCE(CHAR(39)+RTRIM(@User)				+CHAR(39),'NULL')+CHAR(13)+CHAR(10)
	+ ', @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT; '	  +CHAR(13)+CHAR(10)
	+ 'SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]'
  EXEC [isp_LOG_SprocExecLog] @Sprocname, @s, @e, @rc, @Error, @Error_Message, @Rowcount, @User, @Parameters, @sprc OUTPUT	
  SET NOCOUNT OFF
  RETURN

usp_DEL_Category_Err:
  ROLLBACK TRAN
  IF @Error_Type < 50000 
    RAISERROR @Error_Type @Error_Message
  GOTO usp_DEL_Category_Exit

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT VIEW DEFINITION                ON [dbo].[usp_DEL_Category]           TO [myActions_User99_Role]
GRANT EXECUTE                        ON [dbo].[usp_DEL_Category]           TO [myActions_User99_Role]
GRANT VIEW DEFINITION                ON [dbo].[usp_DEL_Category]           TO [myActions_Notes_User99_Role]
GRANT EXECUTE                        ON [dbo].[usp_DEL_Category]           TO [myActions_Notes_User99_Role]
GO

quote:

---

Originally posted by sqlnovice1

like the name, good to know there are another 998 novices out there :-)

---