T-SQL Tuesday #143 – Short Code Examples

It’s another edition of T-SQL Tuesday! This month, John McCormack (b | t) asks us:

“What are your go to handy short scripts”?

I don’t know if I necessarily have a full statement to share, but I definitely I have a snippet that I use a lot that I love:

IF @debug = 1

I often find that I have to write complicated stored procedures where I need to check things as I go along. My go-to for using this snippet is when I write stored procedures that use dynamic SQL. You’d be surprised (or not) at how often I have had to do this over the years. There’s been functionality where the user gets to choose the columns being used, rewriting ORM data layer “catch-all” queries to improve performance, and cross database queries where the name of the database may not be the standard name (think development and QA databases living on the same SQL instance.)

I have a demo stored procedure that I use for my “Practical Starter Guide to SQL Notebooks” session where I show this. The name of my stored procedure is Return_The_Catch_All_Search.

I start by adding a parameter with a default value to my stored procedure:

Parameter list for the stored procedure. The debug parameter is circled.

I purposely have a default value of 0 for the parameter. By default, you shouldn’t need to debug the proc. The idea is that this should only be used for troubleshooting problems. By adding the default, the application can ignore this parameter and pretend it doesn’t exist. It also means it doesn’t have to handle anything it returns if you decide to have the debug option do a SELECT to return data.

Later in the procedure, I add the block to display the dynamic SQL that is created so I can make sure that 1) it created properly and 2) I can copy it into another query window to figure out why it isn’t returning what we expect it to return:

I only need one thing done, so I’m just printing the SQL that was put together.

That’s it. I’m using this parameter as I’m initially creating the stored procedure so it makes sense that I may need to use it later after someone has started testing it in QA or even once it makes it into production. It saves a lot of time instead of having to figure out what my final SQL statement is by changing the stored proc back to a script with this logic.

While I use this code primarily for dynamic SQL, it could be used for any stored proc where you need additional information or would normally stop a script to see results to make sure everything looks as it should.

• For the stored procs with the complicated logic where you would normally troubleshoot by selecting from the variable\temp table\table\SELECT statement\etc, this would be a way to build it those checks inside the proc.
• You could add it in those legacy procs that have massive cursors that shouldn’t be there but you can only troubleshoot them and not rewrite them like you want to. Or maybe they can help you identify the points you can rewrite later…?
• It could be used to add logging in situations where the users are complaining of issues by adding a debug toggle in the application to pass the value to write the information needed to help solve the issue to another table.

It’s just a simple little snippet. I don’t know how or when I started doing this, but I love this trick. I find it incredibly useful for those complex procs that would otherwise a long time to troubleshoot, especially once it’s in the field.

Thanks to John for hosting this month! Looking forward to seeing everyone’s short code examples!