SpDev Install free

How to debug a stored procedure in SQL Server

SSMS no longer has a T-SQL debugger. Here are the ways to debug a SQL Server stored procedure today: Visual Studio, RAISERROR WITH NOWAIT, logging that survives a rollback, and stepping through it in VS Code.

By Areeb Ahmer, . 5 minute read.

SQL Server Management Studio had a T-SQL debugger until version 17.9.1. From SSMS 18 on it was gone, and Microsoft has said it won’t come back. If you learned to debug procedures by pressing Alt+F5 in SSMS, that habit doesn’t work any more.

You still have options: a debugger in Visual Studio, a few T-SQL techniques that work anywhere, and debuggers in other editors. This article goes through them, from the quickest to the most thorough.

The T-SQL examples run against the SpDevDemo database.

Option 1: the Visual Studio T-SQL debugger

Visual Studio still has the debugger SSMS lost, in SQL Server Data Tools (the Data storage and processing workload). In SQL Server Object Explorer, right-click a procedure and choose Debug Procedure.

It works, with conditions:

  • It needs to run as a member of sysadmin on the server.
  • It doesn’t support Azure SQL Database, Azure SQL Managed Instance or SQL database in Fabric.
  • For a remote server, it needs firewall rules and remote debugging configured on both machines.
  • The ARM64 build of Visual Studio 2026 doesn’t include it.

If you already use Visual Studio with SSDT and you’re sysadmin on a local development server, it’s a reasonable choice. Most developers aren’t sysadmin on a shared development server, and installing Visual Studio to debug one procedure is a lot.

Option 2: print what happens, as it happens

The oldest technique is to print values as the procedure runs. PRINT has a catch: its output is buffered and usually arrives only when the batch ends. For a procedure that runs for a minute, you see nothing for a minute.

RAISERROR with severity 0 and WITH NOWAIT sends the message immediately:

DECLARE @rows INT, @started DATETIME2 = SYSDATETIME();

SELECT @rows = COUNT(*) FROM dbo.Orders WHERE Status = 'Open';
RAISERROR('Open orders: %d', 0, 1, @rows) WITH NOWAIT;

DECLARE @ms INT = DATEDIFF(MILLISECOND, @started, SYSDATETIME());
RAISERROR('Step 1 took %d ms', 0, 1, @ms) WITH NOWAIT;
Open orders: 6000
Step 1 took 37 ms

Severity 0 is informational: it doesn’t raise an error or jump to a CATCH block. %d formats an integer and %s a string. The arguments must be variables or constants, not expressions, which is why @ms is computed first.

Leave these in the procedure behind a parameter, so you can switch them on in any environment:

CREATE OR ALTER PROCEDURE dbo.CloseStaleOrders
    @Days  INT,
    @Debug BIT = 0
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @n INT;

    UPDATE dbo.Orders SET Status = 'Cancelled'
    WHERE Status = 'Open' AND OrderDate < DATEADD(DAY, -@Days, CAST(SYSDATETIME() AS date));
    SET @n = @@ROWCOUNT;

    IF @Debug = 1 RAISERROR('Cancelled %d stale orders', 0, 1, @n) WITH NOWAIT;
END

Option 3: look at the intermediate results

Many bugs are in the data a procedure builds up, not in its control flow: a temp table with duplicate rows, a join that drops the rows with a NULL key. With a debug flag, select the intermediate results:

IF @Debug = 1
    SELECT 'after dedupe' AS step, * FROM #Candidates ORDER BY CustomerId;

Each step’s rows appear as a separate result set, labelled by the first column.

Option 4: a log that survives a rollback

Logging to a table is useful for procedures that run unattended, for example from SQL Server Agent. But if the procedure fails and rolls back its transaction, the log rows written inside the transaction are rolled back too: you lose the record of exactly the run you wanted to see.

Table variables are the way round it. A rollback doesn’t undo changes to a table variable, so collect the log in one and write it to the real table after the rollback:

CREATE TABLE dbo.ProcLog (
    LogId    INT IDENTITY PRIMARY KEY,
    LoggedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    Step     NVARCHAR(200) NOT NULL
);
GO

DECLARE @log TABLE (Step NVARCHAR(200));

BEGIN TRY
    BEGIN TRAN;
    INSERT @log VALUES (N'Updating open orders');
    UPDATE dbo.Orders SET Total = Total * 1.1 WHERE Status = 'Open';
    INSERT @log VALUES (CONCAT(N'Updated ', @@ROWCOUNT, N' orders'));
    SELECT 1 / 0;                                    -- something fails
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    INSERT @log VALUES (CONCAT(N'Failed at line ', ERROR_LINE(), N': ', ERROR_MESSAGE()));
    INSERT dbo.ProcLog (Step) SELECT Step FROM @log;  -- after the rollback, so it's kept
END CATCH;

SELECT Step FROM dbo.ProcLog ORDER BY LogId;
Updating open orders
Updated 6000 orders
Failed at line 9: Divide by zero error encountered.

The update was rolled back; the log of it wasn’t.

Option 5: run the body as a script, carefully

A common approach is to copy the procedure’s body into a query window, turn the parameters into DECLAREd variables, and run it a piece at a time. It works for logic bugs. For performance problems it misleads you: SQL Server can’t see the value of a local variable when it compiles a statement, so it estimates from averages, while a real parameter is sniffed. The script can get a different plan from the procedure and be fast while the procedure is slow. If you’re chasing performance, run the procedure itself and look at its actual plan.

Option 6: step through it in VS Code

SpDev is a free VS Code extension that brings back the debugger SSMS lost, without sysadmin rights or Visual Studio. Open the .sql file that contains the procedure and click Debug above CREATE PROCEDURE:

  • Set breakpoints and step with F10, F11 and Shift+F11.
  • See every parameter, variable, temp table and table variable before each statement.
  • Step into the procedures it calls, from your local .sql files.
  • Step back to the statement before, after you’ve passed the interesting moment.
  • Hover a SELECT that has run to see the rows it returned.

SpDev runs an instrumented temporary copy of your local file, so you can edit the procedure and run it again without deploying it. It also works with Azure SQL Database. It can’t change a variable while you’re stopped, because it replays a recording of the run.

Finding where an error happened

When a procedure fails, ERROR_LINE() and ERROR_PROCEDURE() in the CATCH block tell you where. The line number counts from the start of the batch that created the procedure, CREATE PROCEDURE included. If your file starts with comments or a USE statement in an earlier batch, count from the CREATE.

For errors that nothing catches, the message itself carries the procedure and line: Msg 8134, Level 16, State 1, Procedure dbo.CloseStaleOrders, Line 12.

Which one to use

Situation Use
A quick look at a value in a procedure you’re editing RAISERROR ... WITH NOWAIT behind a @Debug flag
Wrong data in the result Select the intermediate temp tables with @Debug = 1
A job that fails at night A table-variable log written after the rollback
Logic you need to follow line by line A debugger: Visual Studio SSDT, or SpDev in VS Code
A procedure that’s slow, not wrong Its actual execution plan; see how to read one