Three ways to debug T-SQL code

Writing new code = bugs. With this, everything is simple.

Getting rid of mistakes is a difficult task.

Programmers are used to having built-in tools in their development tools that show which line of code is currently running, display the current contents of variables, display progress messages, and so on. Some time in SQL Server Management Studio had a code debugger too, but as of SSMS v18, it has been removed. Although even when there was a debugger, I was not a fan of it: SQL Server could literally stop processing other requests while it was executing your request. It was a disaster, especially when your request was blocking other users, and it was all happening on a production base.

I wish we had an easy way to debug T-SQL on a production base without locks, but debugging T-SQL is different from debugging in C#. So if your T-SQL code isn’t doing what you expected, here are some good ways to debug it.

Option 1: Use PRINT

Since time immemorial, developers have inserted lines like this into their code:

BEGIN TRAN
	PRINT 'Starting access date changes'

	UPDATE dbo.Users
		SET LastAccessDate = GETDATE()
		WHERE DisplayName = N'Brent Ozar';

	PRINT 'Done with access date, starting reputation changes'

	UPDATE dbo.Users
		SET Reputation = Reputation / 0
		WHERE DisplayName = N'jorriss';

	PRINT 'Done with reputation changes'
COMMIT

To see which part of the code has an error when it crashes:

This approach has a couple of problems:

PRINT does not display information instantly. SQL Server caches the information that should be displayed in Messages. If you’re debugging a long process, chances are you’d like to see error messages as soon as they occur.
PRINT transmits data over the network whether you like it or not, increasing the overhead of your requests. It doesn’t matter much until you’re over 1,000 requests per second, after which you’ll want to cut costs wherever possible. You really only want to see debug messages when you need them.

Let’s try to improve our debugging with RAISERROR.

Option 2: Use RAISERROR, pronounced raise-roar

What? Did you know that this word is pronounced differently? Okay, I’ll tell you a secret, I didn’t know how to pronounce it either – Greg Low from SQLDownUnder told me about it. Let’s complicate our code:

DECLARE @Debug BIT = 1;

BEGIN TRAN
	IF @Debug = 1
		RAISERROR (N'Starting access date changes', 0, 1) WITH NOWAIT

	UPDATE dbo.Users
		SET LastAccessDate = GETDATE()
		WHERE DisplayName = N'Brent Ozar';

	IF @Debug = 1
		RAISERROR (N'Done with access date, starting reputation changes', 0, 1) WITH NOWAIT

	UPDATE dbo.Users
		SET Reputation = Reputation / 0
		WHERE DisplayName = N'jorriss';

	IF @Debug = 1
		RAISERROR (N'Done with reputation changes', 0, 1) WITH NOWAIT

COMMIT

I have added a variable @Debugand my status messages are now only displayed when @Debug = 1. In this particular example, I don’t need a parameter – but in your working stored procedures and functions, you will definitely need it, just remember to set the default value of this parameter to 0, as in the example below:

CREATE OR ALTER PROC dbo.DoStuff
	@MyParam VARCHAR,
	@Debug BIT = 0 AS
...

In this case, you can enable debugging manually when you need it, and the application will not call the procedure or function with the parameter @Debugso the default value, 0, will always be used.

I also switched to RAISERROR With PRINTbecause RAISERROR there is a useful parameter WITH NOWAITwhich instructs SQL Server to send a status message to the client immediately, without waiting for the buffer to be full.

When you’re debugging a long or complex process, you might want to dynamically manage message statuses. Let’s say you have a stored procedure that runs for hours and you want to understand which part of it is running the longest. You’re not going to sit there with a stopwatch, and you’re not going to come back later hoping your queries are still in the query plan cache. Instead, you want to add the date/time to the message RAISERROR.

Unfortunately, RAISERROR does not support string concatenation. Therefore, you must transfer to RAISERROR just one line that will contain all the information you need, as shown in the example below:

DECLARE @Now NVARCHAR(50);

SET @Now = CONVERT(NVARCHAR(50), GETDATE(), 26);
	RAISERROR (N'Done with reputation changes at %s', 0, 1, @Now) WITH NOWAIT

And this will allow you to get the date and time in the output:

You can even pass multiple arguments – here you can find more information on how to running RAISERROR.

Option 3: Use table variables

You may have heard from me or other developers that using table variables reduces performance. For the most part, this is true – but sometimes they work very quickly, which is exactly what we talked about in this course. Fundamentals of TempDB. However, table variables have one peculiarity: they ignore transactions.

BEGIN TRAN
	DECLARE @Progress TABLE (StatusDate DATETIME2, StatusMessage NVARCHAR(4000));

	INSERT INTO @Progress VALUES (GETDATE(), N'A one');
	INSERT INTO @Progress VALUES (GETDATE(), N'And a two');

ROLLBACK

SELECT * FROM @Progress ORDER BY StatusDate;

Even if the transaction is rolled back, I will still get the values of the table variables:

This is useful when you need:

Debugging a long running process
involved in the process try/catch, begin/commitwithin which something can fall or roll back
Get the result in tabular form, possibly even with multiple columns, XML, JSONetc.

So here they are – 3 ways to debug without using SSMS Debugger. I usually use RAISERROR – it is easy enough to implement and this mechanism will be used forever. Of course, there are many more debugging options, share your favorite ways in the comments.