Today I’m sharing a couple of programming tips for Stored Procedures I have found to be helpful, but not commonly demonstrated.
First, I like to declare local variables in stored procedures, using them like an ENUM in other languages. They do away with magic numbers, or the need to have comments wherever numbers are used. Instead, I declare a variable and assign a value to it.
What prompted this discussion on my use of virtual SQL ENUMs is another technique I use for debugging stored procedures. It is possible to configure Microsoft SQL Server stored procedures so that you can execute each statement in a debug mode. The problem is that when each statement is executed, you can’t necessarily see the results of the statement.
When I have stored procedures that create temporary tables through table variables or # Tables, sometimes it is important to view the contents of those working tables to figure out what is going on with your procedural logic. You can select the contents of those tables while debugging the stored procedure, but you don’t want to have the production version return the data from the debugging steps. So, I add an input parameter to a stored procedure, call it @DEBUG INT = 0 for example.
Then, I use a bit mask to allow me to set @Debug to different values, allowing me to return results in the output from my working table. Let me give a demonstration.
CREATE PROCEDURE prDemoDebug (
@DEBUG INT = 0
)
AS
DECLARE @DebugTable INT = 1
DECLARE @DebugProc INT = 2
SELECT COUNT(1) AS Rows
INTO #TableCount
FROM SYSOBJECTS
WHERE TYPE = ‘U’
SELECT COUNT(1) AS Rows
INTO #ProcCount
FROM SYSOBJECTS
WHERE TYPE = ‘P’
if @DEBUG & @DebugTable = @DebugTable SELECT * FROM #TableCount
if @DEBUG & @ DebugProc = @DebugProc SELECT * FROM #ProcCount
SELECT ‘Table’ as ObjectType, Rows
FROM #TableCount
UNION ALL
SELCT ‘Procs’, Rows
FROM #ProcCount
GO
You always get the final result set which is the union of #TableCount and #RowCount. What’s different is that if you set #Debug to 1, you get the contents of #TableCount and then the union. If @Debug = 2, you get the results of #ProcCount and the union. If @Debug = 3, you get the results of #TableCount, the results of #ProcCount and then the Union of both.
As you can see with this debug technique, you can turn on and off individual result sets by setting a bit for each result set you wish to debug.
You don’t have to always return a result set with your debug techniques. Perhaps you will print the contents of a variable, or time spent in one aspect of your procedure. It’s quite flexible. I have even used this technique to have hard coded debug values for parameters or variables that I don’t want used when running the procedure in production. That way I don’t have to remember to comment them out or take them out.
Using the bit mask, you can have a lot more options if you use a BIGINT data type, because you have 64 options instead of 32. Since my example only uses two values you may not have observed I’m just using the integer representation of a binary bit being set. So the integers you use are a binary sequence, 1, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, …
Using the & operand, I am testing for only a single bit in the entire integer value to be true.
Example: if @DEBUG & @DebugTable = @DebugTable
The following truth table will clarify for those of you who don’t quite remember bit masking
IF 1 & 1 = 1 evaluates to true
IF 1 & 2 = 2 evaluates to false
IF 2 & 2 = 2 evaluates to true
IF 3 & 1 = 1 evaluates to true
IF 3 & 2 = 2 evaluates to true
IF 6 & 1 = 1 evaluates to false
IF 6 & 2 = 2 evaluates to true
IF 6 & 4 = 4 evaluates to true
So, if you have three debug values in your stored procedure, set @Debug to 1 + 2 + 4 (the first three values in the binary sequence).
Cheers,
Ben