Include SProc name in Debug statements

Transact-SQL
1

For debugging purposes all our SProcs have an @IsDebug parameter and then within the code

IF @IsDebug >= 1 SELECT [MySProcName]='DEBUG(1)', [@Param1]=@Param1, ...

so I can just run an SProc with appropriate parameters, set the optional @IsDebug=1 and see whatever "stuff" we decided would be handy when we built the Sproc - or add some more Debug statements and rerun it.

However, if I Copy & Paste a block of code from one Sproc to another (or even from my "Code Snippets" file) it inserts with the wrong name for the target SProc. That's fine if I remember to change them ... but usually I don't.

So then I'm debugging something and looking at the Sproc names ... and scratching my head :frowning:

If the source SProc that the code came from is completely different I realise immediately. If, as happens, its actually a Parent SProc that was split into "Parent and several Child Sprocs" then the SProc name in the Debug statements is that of the parent, whereas in fact its being executed from the Child. Much time is then wasted before I realise my mistake ...

I thought I could have an @SProcName parameter, which would always represent the current Sproc, and then I could change the Debug statement

IF @IsDebug >= 1 SELECT [MySProcName]='DEBUG(1)', [@Param1]=@Param1, ...
to
IF @IsDebug >= 1 SELECT [DEBUG(1)]=@SProcName, [@Param1]=@Param1, ...

but the snag with this is that I do my debugging in TEXT results mode, not GRID, and that makes the column very wide (or I have to size @SProcName appropriated, and SQL is never going to complain if I try to put an over-long string into the @SProcName parameter :frowning: So that's another accident-waiting-to-happen)

Any other options please?

2

I usually use OBJECT_NAME(@@PROCID) to get the name of the current stored proc.

3

What about concatenate the debug output

 if @i_debug = 1 		
 		select 'procName:' + @vc_proc_name
 				+ '; @i_param1:' + cast(@i_param1 as varchar(30))	;

where @vc_proc_name is :
declare @vc_proc_name varchar(100) = object_schema_name(@@procID) +'.'+ object_name(@@procID);

4

For xml path('') it is another approach

if @i_debug = 1 		
	select 'procName:'+object_schema_name(@@procID) +'.'+ object_name(@@procID)
			,';param1:' + cast(@i_count_c as varchar(30))
			,';param2:' + cast(@i_debug as varchar(30))
	for xml path('');

output of it:

procName:dbo.proc_c_2;param1:50;param2:1
5

That's so embarrassing that i didn't think of that! Perfect, thanks.

Didn't know that, much more reliable than

DECLARE @SProcName = 'SomeName'

:slight_smile:

I can;t remember what happens with the FOR XML concatenation trick with NULLs? So CONCAT() was what sprung into my mind.