Edit

Share via

Debug stored procedures

The Transact-SQL debugger allows you to interactively debug stored procedures by displaying the SQL call stack, local variables, and parameters for the SQL stored procedure. The Transact-SQL debugger supports viewing and modifying local variables and parameters, viewing global variables. It also provides the ability to control and manage breakpoints when debugging your Transact-SQL script.

This example shows how to create and debug a Transact-SQL stored procedure by stepping into it.

Note

Transact-SQL debugging isn't available for Azure SQL Database or Azure SQL Managed Instance.

Debug a stored procedure

  1. In the Database Engine Query Editor window, connect to an instance of the SQL Server Database Engine. Select a database in which you can create an example stored procedure.

  2. Paste the following code in the Query Editor.

    CREATE TABLE [dbo].[Products] ([Id] INT, [Name] NVARCHAR(128))

CREATE PROCEDURE [dbo].[AddProduct]
@id INT,
@name NVARCHAR(128)
AS
BEGIN
    INSERT INTO [dbo].[Products] ([Id], [Name]) VALUES (@id, @name)
    SELECT [Name] FROM [dbo].[Products] WHERE [Id] = @id
    DECLARE @nextid INT
    SET @nextid = @id + 1
    INSERT INTO [dbo].[Products] ([Id], [Name]) VALUES (@id, @name)
    SELECT [Name] FROM [dbo].[Products] WHERE [Id] = @nextid
END

  3. Press F5 to run the Transact-SQL code.

  4. In SQL Server Object Explorer, right-click on the same Database Engine and select New Query.... Ensure you're connected to the same database in which you created the stored procedure.

  5. Paste in the following code to the query window.

    EXECUTE [dbo].[AddProduct] 50, N'T-SQL Debugger Test';
GO

  6. Select the left window margin to add a breakpoint to the EXEC statement.

  7. Press the dropdown list arrow on the green arrow button in the Transact-SQL editor toolbar and select Execute with Debugger to execute the query with debugging on.

  8. Alternately, you can start debugging from the SQL menu. Select SQL > Execute With Debugger.

  9. Make sure that the Locals window is opened. If not, select the Debug menu, select Windows and Local.

  10. Press F11 to step into the query. The parameters of the store procedure and their respective values show up in the Locals window. Alternatively, hover your mouse over the @name parameter in the INSERT clause to see the T-SQL Debugger Test value being assigned to it.

  11. Select T-SQL Debugger Test in the textbox. Type Validate Change and press ENTER to change the name variable's value while debugging. You can also change its value in the Locals window. The value of the parameter is red, indicating a change.

  12. Press F10 to step over the remaining code.

  13. When debugging is complete, query the Product table to view its contents.

    SELECT *
FROM [dbo].[Products];
GO

  14. In the results window, notice that new rows exist in the table.

Related content