how-to write proc calls debugging stored procedure

/****** Object:  StoredProcedure [dbo].[procUtils_ProcCallDebug]    

Script Date: 03/23/2010 14:31:38 

Docs at the end: 

******/

IF  EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_ProcCallDebug]') 

AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[procUtils_ProcCallDebug]

GO

/****** Object:  StoredProcedure [dbo].[procUtils_ProcCallDebug]    

See doc bellow 

Script Date: 03/23/2010 14:31:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[procUtils_ProcCallDebug]                            

@ProcName [varchar](200) -- the name of the procedure making the call  

, @ProcStep [varchar](200) = NULL -- optional the procstep such as START , STOP 

, @Msg [varchar](4000) 

, @DebugMsg [varchar](4000)=null 

, @Ret [int] OUTPUT

, @Time datetime= NULL

, @NiceTime varchar(200) = NULL

, @DomainName varchar(200)

, @SqlLogin varchar(200)

AS                            

BEGIN -- proc start                            

SET NOCOUNT ON;                            

declare @ThisProcName varchar(200)        -- you could use this one at the caller's code 

set @ThisProcName = ( SELECT OBJECT_NAME(@@PROCID))        -- which will give you the name of the caller

BEGIN TRY        --begin try                  

set @Ret = 1 --assume that this call of this procedure has failed from the begiing 

INSERT INTO [dbo].[ProcLog]

(

[ProcName] 

, [ProcStep]                    

, [NiceTime]

,[SqlLogin]

,[DomainName]

,[Msg]

,[DebugMsg]

)

VALUES

(

@ProcName

, @ProcStep

,@NiceTime

,@SqlLogin

,@DomainName

,@Msg

,@DebugMsg

)

set @Ret = 0 --tell the caller that the insert was ok 

END TRY        --end try                  

BEGIN CATCH                        

PRINT 'In CATCH block.                         

Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '                        

Error message: ' + ERROR_MESSAGE() + '                        

Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '                        

Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '                        

XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));                        

set @Msg = 'Failed to retrieve Report '             

set @DebugMsg = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) +               

'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) +               

'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + 'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))                        

--record the error in the database                        

set @DebugMsg = @DebugMsg + @Msg    

set @Ret = 1                       

END CATCH                        

return  @Ret                                   

END --PROC END

/*                   

<ProcName> procUtils_ProcCallDebug </ProcName> 

<doc>

Provides a simple logging mechanism to a database with the ProcLog Table 

</doc> 

<use>

DECLARE    @return_value int,

@ProcName varchar(200),

@Msg varchar(4000),

@DebugMsg varchar(4000),

@ProcStep varchar(200) , 

@Ret int

SELECT    @Msg = N'The Msg to the end user'

SELECT    @DebugMsg = N'The debugging msg'

SELECT    @Ret = 1

SELECT @ProcName = 'TheNameOfTheCallingProc or replace with the SELECT OBJECT_NAME(@@PROCID)'

SELECT @ProcStep = 'STOP'

EXEC    @return_value = [dbo].[procUtils_ProcCallDebug]

@ProcName= @ProcName ,

@ProcStep = @ProcStep ,  

@Msg = @Msg ,

@DebugMsg = @DebugMsg ,

@Ret = @Ret OUTPUT,

@NiceTime = N'2010.10.31--12:44',

@DomainName = N'DomainName',

@SqlLogin = N'SqlLogin'

SELECT            @Ret as N'@Ret'

SELECT    'Return Value' = @return_value

GO

SELECT TOP 10 * FROM ProcLog order by 1 desc

</use> */ 

GO

-- START PERMISSIONS 

-- GRANT EXECUTE ON [dbo].[procUtils_ProcCallDebug] TO public AS [dbo]

--GO

-- END PERMISSIONS