Depending on the infrastructure of the application you are working on you may find yourself in need of really being able to interpret what’s going on in the guts of SQL Stored Procedures in a fast and efficient manner.
For a small Stored Procedure you’re probably going to find that, locally at least, the debugging tools with SQL Server Management Tools are going to suit just fine most of the time (i.e. inspecting variables using the locals window, etc). Thinking further along the line however, when your database architecture finds itself going out into the wider world (for example, being hosted on various sites outside of your direct control), or when being debugged for issues by non-developers with permissions not suited to debugging, you’ll need to consider other approaches.
To the end, here’s something I’ve found as a handy pattern to implement when working with Stored Procedures. Provided it’s not over-baked then it normally proves pretty darn useful. So, without further ado, let’s take a look at the simplest possible example of the ‘@DEBUG’ parameter at work.
To set us up we need a very simple set of tables and test data to provide an example case. Below, you’ll see I’ve implemented two tables; tblITEM and tblPARAM. The tblITEM table contains a basic set of items which we will make available via a Stored Procedure. In this implementation, tblPARAM is used to store all of our ‘parameters’ that will ultimately decide how our Stored Procedure behaves. I’ll explain as we go anyway:
--1) Setup the tblPARAM table --If no records exist yet then perform the initial data load. Otherwise, do nothing IF NOT EXISTS ( SELECT * FROM dbo.tblPARAM p ) BEGIN INSERT INTO dbo.tblPARAM ( PARAM_DESC , PARAM_ACTIVE ) VALUES ( 'Active Records Only' , 1 ) , ( 'Big Head Mode' , 0 ); END; --2) Setup the tblITEM table --If no records exist yet then perform the initial data load. Otherwise, do nothing IF NOT EXISTS ( SELECT * FROM dbo.tblITEM i ) BEGIN INSERT INTO dbo.tblITEM ( ITEM_DESC , ITEM_ACTIVE , ITEM_ADD_INFO ) VALUES ( 'Skateboard' , 0 , 'A skateboard with retractable wheels that turns into a hoverboard.' ) , ( 'Super Mario Plumbing Kit' , 1 , 'A red all-purpose plumbing kit.' ) , ( 'Luigi Plumbing Kit' , 1 , 'Just like Mario''s kit, but in green.' ) , ( 'Guitar Strings' , 0 , 'Bog standard strings.' ) , ( 'Simple Item' , 1 , NULL ); END; --Get all Items SELECT i.ITEM_ID , i.ITEM_DESC , i.ITEM_ACTIVE , i.ITEM_ADD_INFO FROM dbo.tblITEM i; --Get all Parameters SELECT p.PARAM_ID , p.PARAM_DESC , p.PARAM_ACTIVE FROM dbo.tblPARAM p;
Now we have our test tables/data in place we now need to author something in order to provide access to this information which, in this case, is a SQL Stored Procedure. The aim here is to provide a basic results set that displays records from tblITEM, with an additional filter on an items active state (based on the relevant tblPARAM setting). The first (buggy as hell!) implementation is as follows; I’m expecting seasoned eyes to pick up on the problem immediately:
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID ('[dbo].[SPROC_GET_ITEMS]','P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[SPROC_GET_ITEMS]
END;
GO
CREATE PROCEDURE [dbo].[SPROC_GET_ITEMS]
AS
BEGIN
--Get a param value, by description, denoting if how we should filter items (just as for illustration purposes)
DECLARE
@ACTIVE_RECORDS_ONLY BIT =
(
SELECT
p.PARAM_ACTIVE
FROM dbo.tblPARAM p
WHERE p.PARAM_DESC = 'Active Record Only'
);
--Get the relevant items
SELECT
i.ITEM_ID
, i.ITEM_DESC
, COALESCE(i.ITEM_ADD_INFO, 'No Additional Information')
FROM dbo.tblITEM i
WHERE i.ITEM_ACTIVE = @ACTIVE_RECORDS_ONLY; --Retrieve active or inactive items, depending on the parameter value
END;
SPROC_GET_ITEMS is geared to pull a value from tblPARAM and then use this to filter a select statement based on tblITEM looking for either active or inactive items. A very simple setup, but even in this case, it’s flawed.
Executing the stored procedure in its current form provides the following results:
Checking the tblPARAM table I can see the parameter that I’m interested in, present and correct with a value of true (1), based on the initial data load I performed. The tblITEM table contains records so what gives…?
As previously mentioned, something of this complexity is probably not going to require you to really think along the following lines, but planning like this early on in a Stored Procedures life-cycle can save future headaches if and when the amount of code begins to stack up.
I tend to like to do the following, and add an @DEBUG input parameter to the Stored Procedure:
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID ('[dbo].[SPROC_GET_ITEMS]','P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[SPROC_GET_ITEMS]
END;
GO
CREATE PROCEDURE [dbo].[SPROC_GET_ITEMS]
(
@DEBUG BIT = 0 --Add a debug parameter (defaulted to false). This can be added when executing the SP for additional information
)
AS
BEGIN
IF (@DEBUG = 1)
BEGIN
PRINT
(
'Running SPROC_GET_ITEMS'
+ CHAR(13)
+ '==============================='
);
END;
DECLARE
@ACTIVE_RECORDS_ONLY BIT =
(
SELECT
p.PARAM_ACTIVE
FROM dbo.tblPARAM p
WHERE p.PARAM_DESC = 'Active Record Only'
);
--Additional debug information
IF (@DEBUG = 1)
BEGIN
--Inspect the parameter value drawn from tblPARAM before utilising in the SELECT statement (to help troubleshoot)
PRINT('Active Records Only parameter value: ' + COALESCE(CONVERT(NVARCHAR(1), @ACTIVE_RECORDS_ONLY), 'NULL (Unset)'));
END;
SELECT
i.ITEM_ID
, i.ITEM_DESC
, COALESCE(i.ITEM_ADD_INFO, 'No Additional Information') AS [ITEM_ADD_INFO]
FROM dbo.tblITEM i
WHERE i.ITEM_ACTIVE = @ACTIVE_RECORDS_ONLY; --Retrieve active or inactive items, depending on the parameter value
END;
This then enables the stored procedure to be called as follows to surface additional, debug information. I prefer to explicitly type the input parameter name as well as specify the value in all cases for clarity, and to satisfy my OCD urges:
--Calling the procedure, expecting just active items....but something is up, now we have debugging we'll hopefully be able to spot the issue EXEC dbo.SPROC_GET_ITEMS 1; --Or, If you like extra keystrokes and less ambiguity EXEC dbo.SPROC_GET_ITEMS @DEBUG = 1;
When called in this way, the code surrounded by the IF checks (seeing if @DEBUG = 1) will be called. We are looking here to grab hold of, via a PRINT statement, the values of any variables being utilised in the procedure. We are not receiving any errors in this scenario so it’s a good bet that something else is afoot and variables are a good place to start. Structuring your debugging like this, from the offset, gives others the opportunity to run your Stored Procedures in a manner that transparently gives access to functional details that will make troubleshooting easier. From a support perspective this can be invaluable. You’ll notice that I also like to include a summary header, only printed if @DEBUG = 1, that normally states something along the lines of ‘Running Stored Procedure SP_NAME’. You can also choose to print out the values of any other Stored Procedure input parameters here just for clarity. If debugging multiple procedures at once this can help in separating the output into sectioned chunks, making it clear which procedure was responsible for what output.
Using a simple COALESCE/CONVERT combination on the @ACTIVE_RECORDS_ONLY variable we should be able to quickly spot if this value is being set as expected.
We have an immediate hit now that illustrates that something isn’t quite right with how we are setting our @ACTIVE_RECORDS_ONLY variable. A scan by eye shows that I’ve gone and royally screwed up! The parameter description is incorrect:
DECLARE @ACTIVE_RECORDS_ONLY BIT = ( SELECT p.PARAM_ACTIVE FROM dbo.tblPARAM p WHERE p.PARAM_DESC = 'Active Record Only' );
Setting p.PARAM_DESC = ‘Active Records Only’ should fix our issue. Let’s look at a fixed, enhanced implementation and evidence supporting that our wonderful little fix is working:
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID ('[dbo].[SPROC_GET_ITEMS]','P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[SPROC_GET_ITEMS]
END;
GO
CREATE PROCEDURE [dbo].[SPROC_GET_ITEMS]
(
@DEBUG BIT = 0 --Add a debug parameter (defaulted to false). This can be added when executing the SP for additional information
)
AS
BEGIN
IF (@DEBUG = 1)
BEGIN
PRINT
(
'Running SPROC_GET_ITEMS'
+ CHAR(13)
+ '==============================='
);
END;
DECLARE
@ACTIVE_RECORDS_ONLY BIT =
(
SELECT
p.PARAM_ACTIVE
FROM dbo.tblPARAM p
WHERE p.PARAM_DESC = 'Active Records Only'
);
--Additional debug information
IF (@DEBUG = 1)
BEGIN
--Inspect the parameter value drawn from tblPARAM before utilising in the SELECT statement (to help troubleshoot)
PRINT('Active Records Only parameter value: ' + COALESCE(CONVERT(NVARCHAR(1), @ACTIVE_RECORDS_ONLY), 'NULL (Unset)'));
END;
SELECT
i.ITEM_ID
, i.ITEM_DESC
, COALESCE(i.ITEM_ADD_INFO, 'No Additional Information') AS [ITEM_ADD_INFO]
FROM dbo.tblITEM i
WHERE
(
i.ITEM_ACTIVE = @ACTIVE_RECORDS_ONLY --Active records only
OR COALESCE(@ACTIVE_RECORDS_ONLY, 0) = 0 --Or all items (Active and Inactive), if the flag is set to 0 (false). Null handled
);
END;
--Only active items at the moment, based on the parameter EXEC dbo.SPROC_GET_ITEMS; GO --Update the parameter to false UPDATE p SET p.PARAM_ACTIVE = 0 FROM dbo.tblPARAM p WHERE p.PARAM_DESC = 'Active Records Only'; GO --Should give us all items based on how this is currently configured EXEC dbo.SPROC_GET_ITEMS;
When you’re done debugging you can just omit the @DEBUG = 1 part of the statement from the Stored Procedure call to trim out any debug output (as the @DEBUG parameter is defaulted to false within the procedure definition).
I’ve made a small adjustment to the where clause on the select statement within the procedure to return all results if the parameter value is 0. Good debug information can sometimes make you rethink a set of statements general structure; something I view as a good thing. The same as doing test-driven development within C# can alter the way you construct your code base, objects and implementation details, writing debug handling into Stored Procedures can help you write more elegantly constructed and robust SQL.
I’ve also decided to treat NULL’s as false from this point forward; this will of course just be down to what is acceptable functionally. It’ll no doubt depend on the business requirements.
Again, this represents the simplest possible way to tie this concept into Stored Procedures. I’ve personally found this approach incredibly handy when dealing with procedures that handle a large number of variables and those that produce very complex dynamic SQL. With the latter this pattern really comes into it’s own, enabling you to print out dynamic SQL to the console to illustrate what is going to be executed, which equates generally to a nice little time saver if problems do occur. Just be careful to not overdo it and use common sense on what to include in any debug output :-).
bearandhammer 