This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to debug T-SQL queries and stored procedures Part one of a four-part series of blogs
This blog gives the low-down on how to start and use the debugger included in SQL Server Management Studio 2008 R2 and SSMS 2012. Debugging SQL stored procedures and queries (this blog) Basic debugging of SQL Using breakpoints Other debugging tools If you'd like to learn more about writing SQL, you can either see our online tutorial (of which this is one small part) or attend one of our SQL courses.

How to debug T-SQL queries and stored procedures

Part one of a four-part series of blogs

This blog gives the low-down on how to start and use the debugger included in SQL Server Management Studio 2008 R2 and SSMS 2012.

Debugging SQL stored procedures and queries (this blog)
Basic debugging of SQL
Using breakpoints
Other debugging tools

If you'd like to learn more about writing SQL, you can either see our online tutorial (of which this is one small part) or attend one of our SQL courses.

Posted by Andy Brown on 06 June 2013

Debugging SQL stored procedures and queries

It sounds useful, doesn't it? Being able to step through your queries line by line to see where any error occurs? This blog will show how to debug in copious detail, but be warned ...

Debugging SQL is nowhere near as useful as debugging a VB or C# program, since each INSERT, DELETE, UPDATE or SELECT statement can not be broken down into smaller parts.

Our Example Query

The following modest query will call a stored procedure to add 5 types of owl into a table, then display the results:

-- try deleting table

BEGIN TRY

DROP TABLE tblOwl

PRINT 'Deleted table'

END TRY

BEGIN CATCH

PRINT 'No table to delete'

END CATCH

-- execute stored procedure to create table of owls

EXECUTE spCreateOwlsTable

-- create an integer variable to hold the number of owls

DECLARE @num int

SET @num = ( SELECT COUNT(*) FROM tblOwl )

-- display this value

SELECT

'Added ' +

CAST(@num AS varchar(10)) +

' owls'

-- display owls added

SELECT

OwlId,

OwlName

FROM

tblOwl

ORDER BY

OwlName

The above SQL calls the following stored procedure (I've put this in so I can show the difference betweeen stepping into and stepping over commands):

CREATE PROC spCreateOwlsTable

-- create new temp table (id column autonumbers rows)

CREATE TABLE tblOwl (

OwlId int PRIMARY KEY IDENTITY(1,1),

OwlName varchar(50)

)

-- add 5 types of owl

INSERT INTO tblOwl(OwlName) VALUES ('Tawny')

INSERT INTO tblOwl(OwlName) VALUES ('Barn')

INSERT INTO tblOwl(OwlName) VALUES ('African fish')

INSERT INTO tblOwl(OwlName) VALUES ('Long-eared')

INSERT INTO tblOwl(OwlName) VALUES ('Wise')

Running the first query should give the following output:

The query shows the number of owls added, then lists them in a table.

So much for our example - time to begin debugging!

This blog assumes that you're using SQL Server 2008 R2 or SQL Server 2012 - the rules are different for earlier versions of SQL. The blog also assumes that you're connected to a database on the same computer. If this isn't the case, you may need to configure the debugger - here are separate articles showing how to do this for SQL Server 2012 and SQL Server 2008 R2.

Parts of this blog
Debugging SQL stored procedures and queries (this blog) Basic debugging of SQL Using breakpoints Other debugging tools

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Debugging SQL stored procedures and queries

Our Example Query

Head office

London

Manchester