SQL FOR JSON Tour

I’ve worked on numerous occasions with the various flavours of the FOR XML clause within SQL Server. Taking that a step further, this seemed like as good a time as ever to check out how to work with the FOR JSON clause.

This clause can, simply put, be used to boil down data retrieved via a SELECT statement into formatted JSON, with a custom structure as required – simples. So, how do we go about this?

To illustrate how this all strings together I’ve scripted together a couple of examples that you can lift and test yourself, as desired. Coming up with examples can be the hardest thing sometimes; in an effort to avoid falling into the classic ‘Person’ table example (Car for C# and so on and so forth 😉 !) I’ve gone with two ‘super imaginative’ (well, maybe not; but give me some credit here!) fictitious relational data setups! Firstly, I use the concept of ‘Robot’ and ‘RobotDanceMoves’ for a look at one-to-one relationships (which gets extended a little later one). Next up, I use the idea of ‘Houses’ to representing a more complex example. Houses have ‘Rooms’ which in turn contain ‘RoomObjects’, this gives us some opportunities to explore one-to-many relationships and how to structure the SQL to get the desired JSON output.

Our test table structures look like this:

Robot Table Structure.

Robot Table Structure.

House Table Structure.

House Table Structure.

We’ll start with utilising the Robot and RobotDanceMoves tables and then use the other tables in the last couple of examples to show a more complex, nested setup.

To get us out of the gate, our first piece of SQL uses FOR JSON PATH in its simplest form, we’re enforcing a one-to-one relationship via the INNER JOIN clause by specifying ‘FavouriteMove = 1’.

SQL:

-- Produce a very simple, flat representation of a Robot and their favourite dance move
SELECT 
r.Id
, r.[Name]
, r.Active
, rdm.[Name] AS [FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON 
	(
		r.Id = rdm.RobotId
		AND rdm.FavouriteMove = 1
	)
FOR JSON PATH;

JSON output:

[
    {
        "Id": 1,
        "Name": "Barry",
        "Active": true,
        "FavouriteDanceMove": "Moonwalk"
    },
    {
        "Id": 2,
        "Name": "Steve",
        "Active": false,
        "FavouriteDanceMove": "Thunder Clap"
    },
    {
        "Id": 3,
        "Name": "Dave",
        "Active": true,
        "FavouriteDanceMove": "Moonwalk"
    },
    {
        "Id": 4,
        "Name": "Zoe",
        "Active": true,
        "FavouriteDanceMove": "The Robot"
    },
    {
        "Id": 5,
        "Name": "Claire",
        "Active": true,
        "FavouriteDanceMove": "The Robot"
    },
    {
        "Id": 6,
        "Name": "Tracey",
        "Active": false,
        "FavouriteDanceMove": "Moonwalk"
    }
]

It is possible to compartmentalise properties further by using column aliases directly to create a key and nested object. Here the ‘Name’ and the ‘Active’ flag for a Robot is nested within ‘RobotCoreDetails’ and the ‘Name’ of a Robots favourite dance move is nested within ‘RobotDanceMove’ as ‘FavouriteDanceMove’.

SQL:

-- Illustration of how the JSON can be manipulated and formatted further by altering column aliases
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON 
	(
		r.Id = rdm.RobotId
		AND rdm.FavouriteMove = 1
	)
FOR JSON PATH;

JSON output:

[
    {
        "Id": 1,
        "RobotCoreDetails": {
            "Name": "Barry",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Moonwalk"
        }
    },
    {
        "Id": 2,
        "RobotCoreDetails": {
            "Name": "Steve",
            "Active": false
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Thunder Clap"
        }
    },
    {
        "Id": 3,
        "RobotCoreDetails": {
            "Name": "Dave",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Moonwalk"
        }
    },
    {
        "Id": 4,
        "RobotCoreDetails": {
            "Name": "Zoe",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "The Robot"
        }
    },
    {
        "Id": 5,
        "RobotCoreDetails": {
            "Name": "Claire",
            "Active": true
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "The Robot"
        }
    },
    {
        "Id": 6,
        "RobotCoreDetails": {
            "Name": "Tracey",
            "Active": false
        },
        "RobotDanceMove": {
            "FavouriteDanceMove": "Moonwalk"
        }
    }
]

As per the FOR XML clause, it is possible to apply a ‘ROOT’ clause. Here, this is used as a wrapper to Robot objects.

SQL:

-- Illustration of adding a ROOT clause to wrap the produced JSON content
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON 
	(
		r.Id = rdm.RobotId
		AND rdm.FavouriteMove = 1
	)
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        }
    ]
}

In this next example, we are moving forward into a one-to-many relationship by removing ‘FavouriteMove = 1’ from the INNER JOIN (I’ve left the alias to say ‘FavouriteDanceMove’ at the moment, but this gets tidied up in a subsequent example). You may be tempted to structure the SQL in this way when getting started but the outcome, as shown below, results in a duplication of the Robot object – not a singular Robot supporting multiple dance moves.

SQL:

-- Represents the incorrect way to produce correctly nested results, on this occassion
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
	INNER JOIN dbo.RobotDanceMoves rdm ON r.Id = rdm.RobotId
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Moonwalk"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "Thunder Clap"
            }
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMove": {
                "FavouriteDanceMove": "The Robot"
            }
        }
    ]
}

This modification correctly provides a single Robot with multiple dance moves. The way to achieve this is by using a subquery SELECT which is directly linked to the outer SELECT (see the ‘WHERE rdm.RobotId = r.Id’ clause). All SELECT statements require a FOR JSON PATH clause to be present. Some additional formatting has been put in place here to output ‘Yep’ or ‘Nope’ to denote if this particular dance move is the Robots favourite.

SQL:

-- Correctly nested results, illustration one
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active] 
,
(
	SELECT
	rdm.[Name]
	, 
	-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
	CASE
		WHEN rdm.FavouriteMove = 1
		THEN 'Yep'
		ELSE 'Nope'
	END AS [FavouriteMove]
	FROM dbo.RobotDanceMoves rdm
	WHERE rdm.RobotId = r.Id
	FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                }
            ]
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Yep"
                }
            ]
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                }
            ]
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                }
            ]
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Yep"
                }
            ]
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Name": "Moonwalk",
                    "FavouriteMove": "Yep"
                },
                {
                    "Name": "Thunder Clap",
                    "FavouriteMove": "Nope"
                },
                {
                    "Name": "The Robot",
                    "FavouriteMove": "Nope"
                }
            ]
        }
    ]
}

Finally, here we have altered the dance move output so that it sits inside a ‘Move’ object. It’s really interesting how aliasing name parts, separated by a period, are directly involved in managing how the content is pushed into nested objects.

SQL:

-- Correctly nested results, illustration two (RobotDanceMoves abstracted into 'Move' using aliases)
SELECT 
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active] 
,
(
	SELECT
	rdm.[Name] AS [Move.Name]
	, 
	-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
	CASE
		WHEN rdm.FavouriteMove = 1
		THEN 'Yep'
		ELSE 'Nope'
	END AS [Move.Favourite]
	FROM dbo.RobotDanceMoves rdm
	WHERE rdm.RobotId = r.Id
	FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');

JSON output:

{
    "Robots": [
        {
            "Id": 1,
            "RobotCoreDetails": {
                "Name": "Barry",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                }
            ]
        },
        {
            "Id": 2,
            "RobotCoreDetails": {
                "Name": "Steve",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Yep"
                    }
                }
            ]
        },
        {
            "Id": 3,
            "RobotCoreDetails": {
                "Name": "Dave",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                }
            ]
        },
        {
            "Id": 4,
            "RobotCoreDetails": {
                "Name": "Zoe",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                }
            ]
        },
        {
            "Id": 5,
            "RobotCoreDetails": {
                "Name": "Claire",
                "Active": true
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Yep"
                    }
                }
            ]
        },
        {
            "Id": 6,
            "RobotCoreDetails": {
                "Name": "Tracey",
                "Active": false
            },
            "RobotDanceMoves": [
                {
                    "Move": {
                        "Name": "Moonwalk",
                        "Favourite": "Yep"
                    }
                },
                {
                    "Move": {
                        "Name": "Thunder Clap",
                        "Favourite": "Nope"
                    }
                },
                {
                    "Move": {
                        "Name": "The Robot",
                        "Favourite": "Nope"
                    }
                }
            ]
        }
    ]
}

In an effort to show a more complex example the following SQL produces nested content illustrating the relationship between ‘Houses’, ‘Rooms’ and ‘RoomObjects’. This uses the same principle of aliasing subqueries and linking inner/outer queries as before.

SQL:

-- Illustration of producing a query that handles one-to-many relationships gracefully
SELECT 
h.Id AS [Id]
, h.[Name] AS [Name]
,
(
	SELECT
	r.Id AS [Id]
	, r.[Name] AS [Name]
	,
	(
		SELECT
		ro.Id AS [Id]
		, ro.[Name] AS [Name]
		FROM dbo.RoomObjects ro
		WHERE ro.RoomId = r.Id
		FOR JSON PATH
	) AS [RoomObjects]
	FROM dbo.Rooms r
	WHERE r.HouseId = h.Id
	FOR JSON PATH
) AS [Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');

JSON output:

{
    "Houses": [
        {
            "Id": 1,
            "Name": "House One",
            "Rooms": [
                {
                    "Id": 1,
                    "Name": "Lounge",
                    "RoomObjects": [
                        {
                            "Id": 1,
                            "Name": "Lamp"
                        },
                        {
                            "Id": 2,
                            "Name": "Sofa"
                        }
                    ]
                },
                {
                    "Id": 2,
                    "Name": "Kitchen",
                    "RoomObjects": [
                        {
                            "Id": 3,
                            "Name": "Knife"
                        },
                        {
                            "Id": 4,
                            "Name": "Kettle"
                        }
                    ]
                }
            ]
        },
        {
            "Id": 2,
            "Name": "House Two",
            "Rooms": [
                {
                    "Id": 3,
                    "Name": "Lounge Diner",
                    "RoomObjects": [
                        {
                            "Id": 5,
                            "Name": "Coffee Table"
                        },
                        {
                            "Id": 6,
                            "Name": "Armchair"
                        }
                    ]
                },
                {
                    "Id": 4,
                    "Name": "Kitchen Utility",
                    "RoomObjects": [
                        {
                            "Id": 7,
                            "Name": "Coffee Machine"
                        },
                        {
                            "Id": 8,
                            "Name": "Microwave"
                        }
                    ]
                }
            ]
        }
    ]
}

To really improve the nesting of the output it is possible to combine and align column and subquery aliases to get a nice, clean structure.

SQL:

-- Example extended with some more aliases to format the structure further 
-- (full nesting of House -> Rooms -> Room -> RoomObjects -> Object)
SELECT 
h.Id AS [House.Id]
, h.[Name] AS [House.Name]
,
(
	SELECT
	r.Id AS [Room.Id]
	, r.[Name] AS [Room.Name]
	,
	(
		SELECT
		ro.Id AS [Object.Id]
		, ro.[Name] AS [Object.Name]
		FROM dbo.RoomObjects ro
		WHERE ro.RoomId = r.Id
		FOR JSON PATH
	) AS [Room.RoomObjects]
	FROM dbo.Rooms r
	WHERE r.HouseId = h.Id
	FOR JSON PATH
) AS [House.Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');

JSON output:

{
    "Houses": [
        {
            "House": {
                "Id": 1,
                "Name": "House One",
                "Rooms": [
                    {
                        "Room": {
                            "Id": 1,
                            "Name": "Lounge",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 1,
                                        "Name": "Lamp"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 2,
                                        "Name": "Sofa"
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "Room": {
                            "Id": 2,
                            "Name": "Kitchen",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 3,
                                        "Name": "Knife"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 4,
                                        "Name": "Kettle"
                                    }
                                }
                            ]
                        }
                    }
                ]
            }
        },
        {
            "House": {
                "Id": 2,
                "Name": "House Two",
                "Rooms": [
                    {
                        "Room": {
                            "Id": 3,
                            "Name": "Lounge Diner",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 5,
                                        "Name": "Coffee Table"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 6,
                                        "Name": "Armchair"
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "Room": {
                            "Id": 4,
                            "Name": "Kitchen Utility",
                            "RoomObjects": [
                                {
                                    "Object": {
                                        "Id": 7,
                                        "Name": "Coffee Machine"
                                    }
                                },
                                {
                                    "Object": {
                                        "Id": 8,
                                        "Name": "Microwave"
                                    }
                                }
                            ]
                        }
                    }
                ]
            }
        }
    ]
}

The full code sample is available here (creation of the tables, test data and some example stored procedures are clubbed together for you to play with as you want):

/*

	FOR JSON PATH - Full code example

*/

PRINT ('FOR JSON PATH - Full code example' + CHAR(13) + '--------------------------------------------');

-- 1) Create the 'JsonForTestDatabase', if required
IF (DB_ID('JsonForTestDatabase') IS NULL)
BEGIN

	PRINT ('Creating the ''JsonForTestDatabase'' sample database.');

	CREATE DATABASE JsonForTestDatabase;

END;
GO

-- 2) Explicitly switch the session to 'point' at the JsonForTestDatabase
USE [JsonForTestDatabase]
GO

-- 3) Create the test tables for example purposes (Robot/House tables)
IF(OBJECT_ID('Robots', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''Robots'' table.');

	CREATE TABLE dbo.Robots
	(
		Id					BIGINT			NOT NULL	PRIMARY KEY		IDENTITY(1, 1)
		, [Name]			NVARCHAR(20)	NOT NULL
		, Active			BIT				NOT NULL	DEFAULT(1)
	);

END;

IF(OBJECT_ID('RobotDanceMoves', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''RobotDanceMoves'' table.');

	CREATE TABLE dbo.RobotDanceMoves
	(
		Id					BIGINT			NOT NULL	PRIMARY KEY		IDENTITY(1, 1)
		, RobotId			BIGINT			FOREIGN KEY (RobotId) REFERENCES dbo.Robots(Id)
		, [Name]			NVARCHAR(20)	NOT NULL
		, FavouriteMove		BIT				NOT NULL	DEFAULT(0)
	);

END;

IF(OBJECT_ID('Houses', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''Houses'' table.');

	CREATE TABLE dbo.Houses
	(
		Id					BIGINT			PRIMARY KEY		IDENTITY(1, 1)
		, [Name]			NVARCHAR(20)
	);

END;

IF(OBJECT_ID('Rooms', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''Rooms'' table.');

	CREATE TABLE dbo.Rooms 
	( 
		Id					BIGINT			PRIMARY KEY		IDENTITY(1, 1)
		, HouseId			BIGINT			FOREIGN KEY (HouseId) REFERENCES dbo.Houses(Id)
		, [Name]			NVARCHAR(20)
	);

END;

IF(OBJECT_ID('RoomObjects', 'U') IS NULL)
BEGIN

	PRINT ('Creating the ''RoomObjects'' table.');

	CREATE TABLE dbo.RoomObjects 
	( 
		Id					BIGINT			PRIMARY KEY		IDENTITY(1, 1)
		, RoomId			BIGINT			FOREIGN KEY (RoomId) REFERENCES dbo.Rooms(Id)
		, [Name]			NVARCHAR(20)
	);

END;

-- 4) Drop/recreate the Utility SPs
IF(OBJECT_ID('GetRobotsWithFavouriteDanceMoveJson', 'P') IS NOT NULL)
BEGIN
	
	PRINT ('Dropping the ''GetRobotsWithFavouriteDanceMoveJson'' stored procedure.');

	DROP PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveJson;

END;

PRINT ('Creating the ''GetRobotsWithFavouriteDanceMoveJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Produce a very simple, flat representation of a Robot and their favourite dance move
	SELECT 
	r.Id
	, r.[Name]
	, r.Active
	, rdm.[Name] AS [FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON 
		(
			r.Id = rdm.RobotId
			AND rdm.FavouriteMove = 1
		)
	FOR JSON PATH;

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetFormattedRobotsWithFavouriteDanceMoveJson', 'P') IS NOT NULL)
BEGIN
	
	PRINT ('Dropping the ''GetFormattedRobotsWithFavouriteDanceMoveJson'' stored procedure.');

	DROP PROCEDURE dbo.GetFormattedRobotsWithFavouriteDanceMoveJson;

END;
	
PRINT ('Creating the ''GetFormattedRobotsWithFavouriteDanceMoveJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetFormattedRobotsWithFavouriteDanceMoveJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Illustration of how the JSON can be manipulated and formatted further by altering column aliases
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active]
	, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON 
		(
			r.Id = rdm.RobotId
			AND rdm.FavouriteMove = 1
		)
	FOR JSON PATH;

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetRobotsWithFavouriteDanceMoveAndRootJson', 'P') IS NOT NULL)
BEGIN

	PRINT ('Dropping the ''GetRobotsWithFavouriteDanceMoveAndRootJson'' stored procedure.');
	
	DROP PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveAndRootJson;

END;

PRINT ('Creating the ''GetRobotsWithFavouriteDanceMoveAndRootJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveAndRootJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Illustration of adding a ROOT clause to wrap the produced JSON content
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active]
	, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON 
		(
			r.Id = rdm.RobotId
			AND rdm.FavouriteMove = 1
		)
	FOR JSON PATH, ROOT('Robots');

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetFullRobotDetailAsJson', 'P') IS NOT NULL)
BEGIN

	PRINT ('Dropping the ''GetFullRobotDetailAsJson'' stored procedure.');
	
	DROP PROCEDURE dbo.GetFullRobotDetailAsJson;

END;

PRINT ('Creating the ''GetFullRobotDetailAsJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetFullRobotDetailAsJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	/*
	-- Represents the incorrect way to produce correctly nested results, on this occassion
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active]
	, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
	FROM dbo.Robots r
		INNER JOIN dbo.RobotDanceMoves rdm ON r.Id = rdm.RobotId
	FOR JSON PATH, ROOT('Robots');

	-- Correctly nested results, illustration one
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active] 
	,
	(
		SELECT
		rdm.[Name]
		, 
		-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
		CASE
			WHEN rdm.FavouriteMove = 1
			THEN 'Yep'
			ELSE 'Nope'
		END AS [FavouriteMove]
		FROM dbo.RobotDanceMoves rdm
		WHERE rdm.RobotId = r.Id
		FOR JSON PATH
	) AS [RobotDanceMoves]
	FROM dbo.Robots r
	FOR JSON PATH, ROOT('Robots');
	*/

	-- Correctly nested results, illustration two (RobotDanceMoves abstracted into 'Move' using aliases)
	SELECT 
	r.Id AS [Id]
	, r.[Name] AS [RobotCoreDetails.Name]
	, r.Active AS [RobotCoreDetails.Active] 
	,
	(
		SELECT
		rdm.[Name] AS [Move.Name]
		, 
		-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
		CASE
			WHEN rdm.FavouriteMove = 1
			THEN 'Yep'
			ELSE 'Nope'
		END AS [Move.Favourite]
		FROM dbo.RobotDanceMoves rdm
		WHERE rdm.RobotId = r.Id
		FOR JSON PATH
	) AS [RobotDanceMoves]
	FROM dbo.Robots r
	FOR JSON PATH, ROOT('Robots');

	-- Return execution status of success
	RETURN 0;

END;
GO

IF(OBJECT_ID('GetHouseDetailAsJson', 'P') IS NOT NULL)
BEGIN

	PRINT ('Dropping the ''GetHouseDetailAsJson'' stored procedure.');
	
	DROP PROCEDURE dbo.GetHouseDetailAsJson;

END;

PRINT ('Creating the ''GetHouseDetailAsJson'' stored procedure.');
GO

CREATE PROCEDURE dbo.GetHouseDetailAsJson
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	/*
	-- Illustration of producing a query that handles one-to-many relationships gracefully
	SELECT 
	h.Id AS [Id]
	, h.[Name] AS [Name]
	,
	(
		SELECT
		r.Id AS [Id]
		, r.[Name] AS [Name]
		,
		(
			SELECT
			ro.Id AS [Id]
			, ro.[Name] AS [Name]
			FROM dbo.RoomObjects ro
			WHERE ro.RoomId = r.Id
			FOR JSON PATH
		) AS [RoomObjects]
		FROM dbo.Rooms r
		WHERE r.HouseId = h.Id
		FOR JSON PATH
	) AS [Rooms]
	FROM dbo.Houses h
	FOR JSON PATH, ROOT('Houses');
	*/

	-- Example extended with some more aliases to format the structure further 
	-- (full nesting of House -> Rooms -> Room -> RoomObjects -> Object)
	SELECT 
	h.Id AS [House.Id]
	, h.[Name] AS [House.Name]
	,
	(
		SELECT
		r.Id AS [Room.Id]
		, r.[Name] AS [Room.Name]
		,
		(
			SELECT
			ro.Id AS [Object.Id]
			, ro.[Name] AS [Object.Name]
			FROM dbo.RoomObjects ro
			WHERE ro.RoomId = r.Id
			FOR JSON PATH
		) AS [Room.RoomObjects]
		FROM dbo.Rooms r
		WHERE r.HouseId = h.Id
		FOR JSON PATH
	) AS [House.Rooms]
	FROM dbo.Houses h
	FOR JSON PATH, ROOT('Houses');

	-- Return execution status of success
	RETURN 0;

END;
GO

-- 5) Insert sample data into the test tables...part 1, robots...
IF NOT EXISTS
(
	SELECT 1
	FROM dbo.Robots
)
BEGIN

	PRINT ('Adding dbo.Robot sample data.');

	INSERT INTO dbo.Robots
	(
		[Name]
		, Active
	)
	VALUES
	('Barry', 1)
	, ('Steve', 0)
	, ('Dave', 1)
	, ('Zoe', 1)
	, ('Claire', 1)
	, ('Tracey', 0);

END;

IF NOT EXISTS
(
	SELECT 1
	FROM dbo.RobotDanceMoves
)
BEGIN

	PRINT ('Adding dbo.RobotDanceMoves sample data.');
	
	--If RobotDanceMoves have been inserted yet the produce a mock sample of randomised data where Robots receive
	--all three dance moves but a 'favourite' on the fly
	INSERT INTO dbo.RobotDanceMoves
	(
		 RobotId
		 , [Name]
		 , FavouriteMove
	)
	SELECT 
	r.Id
	, rm.DanceMove
	, rm.FavouriteMove
	FROM dbo.Robots r
		CROSS APPLY 
		( 
			SELECT 
			TOP 3 
			am.DanceMove
			, 
			CASE 
				WHEN ROW_NUMBER() OVER (ORDER BY NEWID()) = 3
				THEN CAST(1 AS BIT)
				ELSE CAST(0 AS BIT)
			END AS [FavouriteMove]
			FROM
			(
				SELECT
				'Moonwalk' AS [DanceMove]
				UNION
				SELECT
				'Thunder Clap'
				UNION
				SELECT
				'The Robot'
			) AS am
			WHERE r.Id = r.Id
			ORDER BY NEWID()
		) AS rm;

END;

--...Part two, houses (very fixed, just as an illustration)
IF NOT EXISTS
(
	SELECT 1
	FROM dbo.Houses
)
BEGIN

	PRINT ('Adding dbo.Houses sample data.');

	INSERT INTO dbo.Houses
	(
		[Name]
	)
	VALUES
	('House One')
	, ('House Two');

END;

IF NOT EXISTS
(
	SELECT 1
	FROM dbo.Rooms
)
BEGIN

	PRINT ('Adding dbo.Rooms sample data.');

	INSERT INTO dbo.Rooms
	(
		HouseId
		, [Name]
	)
	VALUES
	(1, 'Lounge')
	, (1, 'Kitchen')
	, (2, 'Lounge Diner')
	, (2, 'Kitchen Utility');

END;

IF NOT EXISTS
(
	SELECT 1
	FROM dbo.RoomObjects
)
BEGIN

	PRINT ('Adding dbo.RoomObjects sample data.');

	INSERT INTO dbo.RoomObjects
	(
		RoomId
		, [Name]
	)
	VALUES
	(1, 'Lamp')
	, (1, 'Sofa')
	, (2, 'Knife')
	, (2, 'Kettle')
	, (3, 'Coffee Table')
	, (3, 'Armchair')
	, (4, 'Coffee Machine')
	, (4, 'Microwave');

END;
GO

--6) Execute all of the sample stored procedures
PRINT (CHAR(13) + 'Processing complete...running example stored procedures...');

EXEC dbo.GetRobotsWithFavouriteDanceMoveJson;
EXEC dbo.GetFormattedRobotsWithFavouriteDanceMoveJson;
EXEC dbo.GetRobotsWithFavouriteDanceMoveAndRootJson;
EXEC dbo.GetFullRobotDetailAsJson;
EXEC dbo.GetHouseDetailAsJson;

I hope there are some valuable and useful ideas in this post and, until the next time, happy coding.

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!

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