Effective debugging in T-SQL

USE [GenApp]

GO

/****** Object:  Table [dbo].[DebugStore]    Script Date: 07/07/2009 10:24:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DebugStore](

[DebugStoreId] [int] IDENTITY(1,1) NOT NULL,

[NiceTime] [varchar](200) NULL,

[CopyPasteSnippet] [ntext] NULL,

[Domain_User] [varchar](200) NULL,

[Msg] [varchar](4000) NULL,

[DebugLevel] [int] NULL,

[DebugMsg] [varchar](4000) NULL,

[PageName] [varchar](200) NULL,

[ClassName] [varchar](200) NULL,

[MethodName] [varchar](200) NULL,

[MethodNameGui] [varchar](4000) NULL,

[Ret] [int] NULL,

[LineNumber] [int] NULL,

[LineNumberGui] [int] NULL,

[ProcedureName] [varchar](200) NULL,

[ProcedureStep] [varchar](4000) NULL,

[RecordType] [varchar](50) NULL,

[LogLevel] [int] NULL,

[TimeStamp] [timestamp] NOT NULL,

CONSTRAINT [PK_DebugStore] PRIMARY KEY CLUSTERED 

(

[DebugStoreId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

USE [GenApp]

GO

/****** Object:  StoredProcedure [dbo].[WriteDebug]    Script Date: 07/07/2009 10:25:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[WriteDebug] (                  

@DomainUser varchar(200)=null ,    

@Msg varchar(200)=null ,    

@DebugLevel varchar(200)=null ,    

@DebugMsg varchar(200)=null ,     

@PageName varchar(200)=null ,    

@ClassName varchar(200)=null ,    

@MethodName varchar(200)=null ,    

@MethodNameGui varchar(200)=null ,    

@Ret int  =null ,    

@LineNumber int =null ,    

@LineNumberGui varchar(200)=null ,    

@ProcedureName varchar(200)=null ,    

@ProcedureStep varchar(200)=null ,    

@RecordType varchar(200)=null ,    

@LogLevel int =null     ,

@CopyPasteSnippet ntext = null

)                  

AS                  

begin                   

insert into DebugStore(    

NiceTime ,     

Domain_User ,     

Msg ,     

DebugLevel ,     

DebugMsg ,     

PageName ,     

ClassName ,     

MethodName ,     

MethodNameGui ,     

Ret ,     

LineNumber ,     

LineNumberGui ,     

ProcedureName ,     

ProcedureStep ,     

RecordType ,     

LogLevel    ,

CopyPasteSnippet

) values (     

dbo.funcGetNiceTime()     

, @DomainUser      

, @Msg    

, @DebugLevel    

, @DebugMsg    

, @PageName    

, @ClassName    

, @MethodName    

, @MethodNameGui    

, @Ret    

, @LineNumber    

, @LineNumberGui    

, @ProcedureName    

, @ProcedureStep    

, @RecordType    

, @LogLevel        

, @CopyPasteSnippet      

)    

if ( @@error=0 )                  

begin                   

set @ret = 0                  

set @DebugMsg = 'ok'                  

return @ret                  

end                  

else                   

begin                  

set @ret = 1                  

set @DebugMsg = 'nok'                  

return @ret                  

end                  

end              

/*  

-- USAGE   

USE [GenApp]  

GO  

DECLARE @return_value int  

EXEC @return_value = [dbo].[WriteDebug]  

@Domain_User = N'DomainUser',  

@Msg = N'Msg',  

@DebugLevel = 1,  

@DebugMsg = N'DebugMsg',  

@PageName = N'PageName',  

@ClassName = N'ClassName',  

@MethodName = N'MethodName',  

@MethodNameGui = N'MethodNameGui',  

@Ret = 0,  

@LineNumber = 21,  

@LineNumberGui = 21,  

@ProcedureName = N'ProcedureName',  

@ProcedureStep = N'ProcedureStep',  

@RecordType = N'RecordType',  

@LogLevel = 1  

SELECT 'Return Value' = @return_value  

GO  

select * from DebugStore order by 1 desc  

*/

GO