what I have been doing lately on SqlServer 2005 2008

USE [Db]

GO

/****** Object:  StoredProcedure [dbo].[procUtils_WhatIHaveBeenDoing]    Script Date: 09/18/2009 11:36:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[procUtils_WhatIHaveBeenDoing]

WITH EXECUTE AS CALLER

AS

BEGIN -- proc start                            

SET NOCOUNT ON;                            

declare @procedureName varchar(200)        

declare @procStep varchar(4000)  

declare    @UserSessionId [int]

declare    @PageTypeId [int]

declare    @msgOut [varchar](200)

declare    @debugMsgOut [varchar](200)

declare    @ret [int] 

set @procedureName = ( SELECT OBJECT_NAME(@@PROCID))        

set @procStep = ' '     

BEGIN TRY        --begin try                  

set @ret = 1 --assume false from the beginning                  

declare @LogLevel int --THE LOG LEVEL OF THE EXECUTING USER

select  o.name  , 

(SELECT [definition] AS [text()] 

FROM sys.all_sql_modules 

where sys.all_sql_modules.object_id=a.object_id FOR XML PATH(''), TYPE) 

AS Statement_Text

, a.object_id, o.modify_date  from sys.all_sql_modules  a 

left join sys.objects  o on a.object_id=o.object_id order by 4 desc

--select * from sys.objects

set @LogLevel = dbo.funcGetUserLogLevelByUserSessionId( @UserSessionId)

set @procStep = 'START PROCEDURE ' + @procedureName

if ( @LogLevel = 1 )

begin --if ( @LogLevel = 1 )

INSERT INTO [dbo].[LogStore] ( [Date],[Thread],[Level],[Logger],[Message])

values ( getdate() , N'8', N'DEBUG', @procedureName , @procStep )

END --if ( @LogLevel = 1 )  

set @procStep = ' PUT HERE THE STEP''S NAME '   

if ( @LogLevel = 1 )

begin --if ( @LogLevel = 1 )

INSERT INTO [dbo].[LogStore]([Date], [Thread], [Level], [Logger], [Message])

values( GETDATE(), N'8', N'DEBUG', @procedureName , @procStep )

END --if ( @LogLevel = 1 )  

-- RUN SOME CODE 

-- IF NEEDED PERFORM SOME DEBUGGING 

--GET SOME MESSAGE OF THE DESIRED LANGUAGE FROM THE MSG TABLE 

DECLARE    @return_value int,

@MsgTxt nvarchar(max)

EXEC    @return_value = [dbo].[procUtils_GetMsgTxtByKeyAndUserSessionId]

@UserSessionId = @UserSessionId,

@MsgKey = N'ErrorMsgMenuRetrievalFailed',

@MsgTxt = @MsgTxt OUTPUT

set @ret = @@ERROR                  

if ( @@ERROR != 0 ) 

set @msgOut = @MsgTxt            

return @RET                   

END TRY        --end try                  

BEGIN CATCH                        

EXEC    @return_value = [dbo].[procUtils_GetMsgTxtByKeyAndUserSessionId]

@UserSessionId = 2,

@MsgKey = N'ErrorMsgMenuRetrievalFailed',

@MsgTxt = @MsgTxt OUTPUT

set @ret = 1                  

set @debugMsgOut = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +               

'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' + 

CAST(ERROR_SEVERITY() AS varchar(10)) +               

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

'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))                        

-- record the error in the database                        

set @debugMsgOut = @debugMsgOut + @msgOut            

INSERT INTO [dbo].[LogStore] ( [Date],[Thread],[Level],[Logger],[Message])

values ( getdate() , N'8', N'DEBUG', 'dbo.procUtils_WhatIHaveBeenDoing' , @debugMsgOut )

END CATCH                        

END --procedure end                             

/* 

USAGE:

EXEC procUtils_WhatIHaveBeenDoing

*/

GO