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
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 So that's another accident-waiting-to-happen)
Any other options please?