T-SQL MERGE…Completely overlooked!

Good evening all,

Umm…how an earth did I miss this language feature? Where have I been since the release of SQL Server 2008? This really did just pass me by. I’m in the position whereby I don’t often run into this situation; where a T-SQL MERGE statement is directly applicable (i.e. table synchronisation), but still, I was a little embarrassed. Perhaps I have seen this at some point and it’s just been pushed out of my noodle at the expense of some other piece of information. So, red-faced (at least I am), let’s jump right in.

What is it?

In simple terms, this provides a compound way to perform an INSERT, UPDATE or DELETE on a target table, based on a source table. It’s as easy as that; we’ve negated the need for existence checks and other wrapping code/constructs, including the multiple INSERT, UPDATE and DELETE clauses of course. Performance is mentioned on the appropriate MSDN page. However, in my limited testing, I have not yet seen any major issues; just keep this thought on your mental radar anyway.

More information can be found here, so dive in and have a read (which details performance tips, so keep this in mind): T-SQL MERGE

MERGE Example

The following script rigs up some test data, in two example ‘target’ and ‘source’ tables, in an effort to demonstrate the synchronisations capabilities of the MERGE statement (apologies for the colour coding issues, I am using the correct language type but it seems to be taking exception to some of the content!).

/* 
	Very basic MERGE example
	-----------------------------

	Imagine these tables exist across databases, etc. and require synchronisation (or, as per my recent usage, I built up a derived table using CTE's to provide a source data table to compare against the target table to drive modifications)
	NOTE: Tables created for illustration only; not representative of how to structure this in practice of course
*/

-- TARGET TABLE
IF OBJECT_ID('dbo.TARGET_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.TARGET_PERSON_TABLE
	(
		TARGET_PERSON_ID				INT		PRIMARY KEY
		, TARGET_FORENAME				NVARCHAR(255)
		, TARGET_SURNAME				NVARCHAR(255)
		, TARGET_AGE					INT			
	);

END;

-- SOURCE TABLE
IF OBJECT_ID('dbo.SOURCE_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.SOURCE_PERSON_TABLE
	(
		SOURCE_PERSON_ID				INT		PRIMARY KEY
		, SOURCE_FORENAME				NVARCHAR(255)
		, SOURCE_SURNAME				NVARCHAR(255)
		, SOURCE_AGE					INT			
	);

END;

--Arrange some test data into each table (target and source, for illustration)
INSERT INTO dbo.TARGET_PERSON_TABLE
(
	TARGET_PERSON_ID
	, TARGET_FORENAME
	, TARGET_SURNAME
	, TARGET_AGE
)
VALUES
(
	1
	, 'Dave'
	, 'Jones'
	, 32
)
,
(
	2
	, 'Moira'
	, 'Stevens'
	, 27
)
,
(
	3
	, 'Larry'
	, 'Bodsworth'
	, 48
);

INSERT INTO dbo.SOURCE_PERSON_TABLE
(
	SOURCE_PERSON_ID
	, SOURCE_FORENAME
	, SOURCE_SURNAME
	, SOURCE_AGE
)
VALUES
(
	2
	, 'Mandy'
	, 'Stevens'
	, 32
)
,
(
	3
	, 'Larry'
	, 'Rodsworth'
	, 50
)
,
(
	4
	, 'Sandy'
	, 'Ennis'
	, 29
)
,
(
	5
	, 'Wendy'
	, 'Wainwright'
	, 40
);

-- Inspect the target/source table data prior to the MERGE operation
SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Synchronise the target table with the source table, performing matching and INSERT, UPDATE and DELETE operations as required
MERGE dbo.TARGET_PERSON_TABLE tpt
USING SOURCE_PERSON_TABLE spt ON tpt.TARGET_PERSON_ID = spt.SOURCE_PERSON_ID 
	-- If a row is 'matched' (based on the above 'ON' statement) then simply update the target Person
	WHEN MATCHED 
	THEN UPDATE 
		SET 
		tpt.TARGET_FORENAME = spt.SOURCE_FORENAME
		, tpt.TARGET_SURNAME = spt.SOURCE_SURNAME
		, tpt.TARGET_AGE = spt.SOURCE_AGE
	-- If a row is 'not matched' (based on the above 'ON' statement) then do an insert of a new Person into the target table, based on the source table
	WHEN NOT MATCHED
		THEN INSERT
		(
			TARGET_PERSON_ID
			, TARGET_FORENAME
			, TARGET_SURNAME
			, TARGET_AGE
		)
		VALUES
		(
			SOURCE_PERSON_ID
			, SOURCE_FORENAME
			, SOURCE_SURNAME
			, SOURCE_AGE
		)
	-- Lastly, if a the target table contains a row not matched by the source table then remove the target table row entirely
	WHEN NOT MATCHED BY SOURCE 
		THEN DELETE;

/*
	Inspect the target/source table data post MERGE operation (values should be synchronised between correctly between the two tables)

	Expected Results
	----------------------
	1) Person ID 1, Dave Jones, should be removed from the target table (DELETE)
	2) Person ID 2, Forename of 'Moira' should be updated to 'Mandy' and Age should be updated from 27 to 32 (UPDATE)
	3) Person ID 3, Surname of 'Bodsworth' should be updated to 'Rodsworth' and Age should be updated from 48 to 50 (UPDATE)
	4) Person ID 4, Sandy Ennis, should be added to the target table (INSERT)
	5) Person ID 5, Wendy Wainwright, should be added to the target table (INSERT)

*/

SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Clear down the tables post operation, for ease of re-running and re-testing
TRUNCATE TABLE dbo.TARGET_PERSON_TABLE;
TRUNCATE TABLE dbo.SOURCE_PERSON_TABLE;

Here is the state of the target and source tables prior to the MERGE statement being executed:

Illustrates the target and source tables state after the merge process

Target and Source Tables Post Merge.

After execution, you can see the effects of the synchronisation between the target and source tables:

Illustrates the target and source tables state before the merge process

Target and Source Tables Prior to Merging.

If this was useful to you then that’s superb…my guess is that a fair few of you will be shaking your heads wondering which hole in the sand I’ve had my head planted in for the last few years. Well, I call it how I see it and I’m pretty sure I missed the memo, so for you others out there that may have missed this, enjoy!

I’m still playing around with a F#/FitBit related piece at the moment, and I really want it to be a good quality post, so hang on in there as it’s coming soon.

Cheery bye!

A Little FOR XML PATH Nugget

A very small post this one, covering a little nugget that I’d almost forgot until it came up trumps again this week; TSQL FOR XML PATH can be a nice solution for concatenation of strings across rows (in a given column).

It’s fairly common to have the need to concatenate column based data, as the following example illustrates:

--Standard concatenation of column values (comma separated, produces multiple rows)
SELECT 
FORENAME 
+ ', ' 
+ SURNAME AS [FULL_NAME]
FROM dbo.tblTEST t
WHERE t.ID < 4;

However, don’t forget that working through and concatenating row based data, in a particular column, can be achieved simply using the FOR XML PATH construct, just like this:

--Concatenation of row values for a particular column (imagine we wanted comma separated forenames for example) - Provides a single column as structured
SELECT
CONVERT                                     --Conversion required to an NVARCHAR(VALUE) (MAX depending on string size) - The result will be XML when using FOR XML PATH initially
(
    NVARCHAR(100)
    ,
    (
        SELECT
        t.FORENAME + 
        CASE
            WHEN t.ID < 3                    --Don't add a comma after the last value (just for illustration purposes)
            THEN ', '
            ELSE ''
        END
        FROM dbo.tblTEST t
        WHERE t.ID < 4
        FOR XML PATH('')					 --Specify FOR XML PATH using an empty string (we don't want a wrapping element when concatenating strings)
    )
) AS [COMMA_SEPARATED_FORENAMES];

Interesting use of FOR XML PATH that’s well worth keeping in mind, it can come in dead handy. Apologies for the Short and sweet post; it’s the order of the day! I’ve managed to pick up the dreaded lurgy so I’m dosed up on medication and drinking a tonne of coffee! Here’s hoping that this post makes sense when I read it later on.

Until the next time, bye for now!

SQL – More Expansive Object Searching

Evening all,

I thought this would be worth sharing, in its current form. It’s a small piece of SQL (this would be encapsulated in a Stored Procedure) that allows you to search through a host of objects encompassing Stored Procedures, Functions, Views, Triggers and even Indexes for a specific search term (i.e. an object name).

I’ve utilised a CTE here (just for readability more than anything; a small sampling of actual Execution Plans suggest this isn’t hurting performance to structure the code in this way) to pull through the full object definition, for any matches, where types easily support it. Indexes are the tricky ones to deal with here; from what I’ve gathered the definition would need to be manually constructed; hence, I’ve omitted generating a definition for now.

This is essentially a bit of an extension of some rougher and readier code put together in my SQL Debugging Tips – Part Two post:

The code is as follows (would be appropriately wrapped in a Stored Procedure):

SET NOCOUNT ON;

--1) Validation - Ensure that a valid Search Term has been supplied (cannot be NULL or an empty string)
IF (COALESCE(@SEARCH_TERM, '') = '')
BEGIN
	 
	  RAISERROR('@SEARCH_TERM cannot be an empty string or NULL. Please specify a valid value (procedure: YOUR_PROCEDURE_NAME).', 16, 1);
	  RETURN;       --Input invalid, simply return

END;

--Enable a 'fuzzy' match
SET
@SEARCH_TERM = '%' + @SEARCH_TERM + '%';

--CTE that is used to 'bundle' up all of the various objects being search (for the given Search Term)
WITH COMBINED_OBJECTS
(
	  OBJ_ID
	  , OBJ_NAME
	  , OBJ_TYPE
	  , OBJ_DEFINITION
)
AS
(
	  --Search Procedures, Functions, Triggers and Views for the Search Term (in the actual definition of the object)
	  SELECT
	  sm.object_id AS [OBJ_ID]
	  , so.name AS [OBJ_NAME]
	  , so.type_desc AS [OBJ_TYPE]
	  , sm.definition AS [OBJ_DEFINITION]
	  FROM sys.sql_modules sm
			 INNER JOIN sys.objects so ON sm.object_id = so.object_id
	  WHERE sm.definition LIKE @SEARCH_TERM
	  UNION ALL
	  --Search for the Search Term in the name of an index
	  SELECT
	  si.object_id AS [OBJ_ID]
	  , si.name AS [OBJ_NAME]
	  , 'INDEX - ' + si.type_desc COLLATE DATABASE_DEFAULT AS [OBJ_TYPE]		                                                         --Negate collation issues with concatenation														
	  , NULL AS [OBJ_DEFINITION]
	  FROM sys.indexes si
	  WHERE si.name LIKE @SEARCH_TERM
	  UNION ALL
	  --Search for the Search Term in the physical column names that comprise an index definition
	  SELECT
	  si.object_id AS [OBJ_ID]
	  , sc.name + ' (' + si.name COLLATE DATABASE_DEFAULT + ' - ' + si.type_desc COLLATE DATABASE_DEFAULT + ')' AS [OBJ_NAME]            --Negate collation issues with concatenation
	  , 'INDEX_COLUMN' AS [OBJ_TYPE]
	  , NULL AS [OBJ_DEFINITION]
	  FROM sys.indexes si
			 INNER JOIN sys.index_columns sic ON
			 (
				   si.object_id = sic.object_id
				   AND si.index_id = sic.index_id
			 )
			 INNER JOIN sys.columns sc ON
			 (
				   sic.object_id = sc.object_id
				   AND sic.column_id = sc.column_id
			 )
	  WHERE sc.name LIKE @SEARCH_TERM
)
--Return the results to the caller (can be expanded as needed)
SELECT
co.OBJ_ID
, co.OBJ_NAME
, co.OBJ_TYPE
, co.OBJ_DEFINITION
FROM COMBINED_OBJECTS co
ORDER BY co.OBJ_TYPE, co.OBJ_NAME;      --Do a little bit of ordering to make the results easier to digest                                                                                                                                                 

With Indexes, I’ve allowed the ability for a developer to search within the name of the Index and search for hits linked to the columns which comprise the Index.

This is all based on a ‘fuzzy’ search (aka using Wild Cards) – @SEARCH_TERM would be an input parameter of the Stored Procedure of type NVARCHAR. A slight spin on a basic check on sys.procedures and a little more expansive.

Just a small post to get this off my chest.

Cheers!

Back Online: Normal Service Resumed

I’m back from my hiatus which encompassed getting married, eating far too much food and drinking wine and beer on the wonderful Adriatic coast. It’s time to get back to some serious coding and perhaps reconsider the longer term plans for this blog.

To start us off, I’ve been contemplating pushing a little money into this to sharpen up the experience a little and will most likely give the blog some dedicated presence on Facebook/Twitter. Why do it by halves; I’ll go balls deep and hope for the best!

There are numerous items that I previously wanted to, and still plan on, covering but other nuggets of technology have caught my eye in the interim. In addition to just writing code, I would also like to reflect on my own methodologies for learning subject matter and trying to improve comprehension as I progress on this journey. Anything I do to this end will get ‘air time’ within this blog and I’ll you all know if I come across anything that works particularly well (or falls flat on its face!) as and when it happens.

Lastly, although not strictly ‘code’ based, my wife (weird to say that!) plans on starting her own business this year so it provides us both with an opportunity to reimagine our workspace in the home. The plan is to turn our crap-hole of a box room into a useable work area; as we get stuck into this I’ll post updates to show how this evolves.

As we all know, putting something down on paper (or the internet!) is the first step on any journey. Here’s the redefined hubs of activity as I see them covering things you can expect to see on this blog in 2015/2016.

  • Reimagining of the Blog and some kind of dedicated presence on Facebook/Twitter.
  • Changes to our home workspace to show you how this progresses.
  • Updates covering any learning techniques as I study them. If these are useful to coding then expect them to get ‘air time’. For starters, look out for:
  • Coverage on the following topics (not sure on how basic/advanced this will be – Most likely this will comprise of feelers into a topic unless something really takes my fancy):
    • Finishing off the Epic Quest project.
    • F# Forays.
    • Coverage of Python.
    • Some further raw JavaScript coverage including jQuery.
    • Hello World level Raspberry Pi.
    • Coding against the Leap Motion Controller API.
    • Xamarin Tools.
    • ASP.NET MVC.
    • My friend Pete has written a superb object-orientated take on JavaScript – Picket.JS.
    • Further C# Unity game development (I still see myself covering a larger scale project encompassing the use of Blender, Gimp and Unity to make a standalone title).
    • Posts covering C# and TSQL (I’ve done some MySQL work recently so I will incorporate this into the proceedings if possible) as interesting topics catch my eye.
    • WPF (Rooting around in XAML) as time allows.

In and around this, I’m starting to sniff around the idea of completing full Microsoft Certifications in the next year to year and a half, so as I hop hurdles surrounding this I’ll give you all of the details.

This is not really designed to be a personal ransom note and I’m not going to outright hold myself to completing all of these things, but I do want to make a commitment to producing content as and when I can and keeping this fun (for myself and anyone reading along).

All that’s left to say is wish me luck and watch this space!

SQL Debugging Tips – Part Two

A common task within SQL is to search an objects definition for a particular reference. This might be a table, column, procedure, function or view name. Below are a few techniques for achieving this. I’ve also highlighted some tables that are related to SQL Server Maintenance Plans and how to start searching interrogating the data tied to these.

/* 

Searching Object Definitions in SQL.

sys.sql_modules is a fairly complete way to achieve this but there's a myriad of options below, including a way 
to look into SQL Maintainence plans (add a where clause on the command column to filter this down)

*/

DECLARE
@SEARCH_TERM		NVARCHAR(MAX) = '%tblSYS_SCHEMA_COLUMN%';

--Full raw objects that reference tempID (or the SEARCH_TERM) in Views (for reference, select only), Triggers, SP's and Functions
SELECT
so.name
, sm.[definition]
, so.type_desc
FROM sys.sql_modules sm
	INNER JOIN sys.objects so ON sm.object_id = so.object_id
WHERE sm.[definition] LIKE @SEARCH_TERM
ORDER BY so.type_desc, so.name;

--Raw results from sys.procedures (SP's only) - Use for verification with the above sys.sql_modules results
SELECT *
FROM sys.procedures sp
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE @SEARCH_TERM
ORDER BY sp.name;

--Results from the INFORMATION_SCHEMA.ROUTINES system view (encapsulating SP's and Functions) - Use for verification with the above sys.sql_modules results
SELECT
ir.SPECIFIC_NAME
, ir.ROUTINE_TYPE
, ir.*
FROM INFORMATION_SCHEMA.ROUTINES ir
WHERE ir.ROUTINE_DEFINITION LIKE @SEARCH_TERM
ORDER BY ir.ROUTINE_TYPE DESC, ir.SPECIFIC_NAME;

--Check SQL Jobs (Maintenance Plans) for the search term being imbedded in the 
SELECT
sj.name
, sj.[enabled]
, sjs.step_name
, sjs.subsystem
, sjs.command		--Filter the command (by adding a WHERE clause) column by the search term (TSQL subsystems)
FROM msdb.dbo.sysjobs sj
	INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.command LIKE @SEARCH_TERM
ORDER BY sj.name;

/*
SQL JOB TABLES FOR REFERENCE

SELECT * FROM msdb.dbo.SysJobs
SELECT * FROM msdb.dbo.SysJobSteps
SELECT * FROM msdb.dbo.SysJobSchedules
SELECT * FROM msdb.dbo.SysJobServers
SELECT * FROM msdb.dbo.SysJobHistory
*/

SQL Debugging Tips – A Simple Example

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:

TEST_DB Structure

TEST_DB Structure

--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:

SP call with no results.

SP call with no 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.

SP call with no results but debugging activated.

SP call with no results but debugging activated.

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;
SP results after fix.

SP results after fix.

SP messages after fix.

SP messages after fix.

--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;
A full test.

A full test.

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 :-).