THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Stored procedure debugging in SSDT and LocalDB

Stored procedure debugging is a feature that has existed in SQL Server Management Studio (SSMS) for some time (since before SQL Server 2005 I believe) yet in my experience not many folks make use of it, indeed many folks aren’t even aware it exists. In this post I shall try and shine a light on what I believe is an underused feature in the SQL Server toolset.

When I have attempted to use debugging within SSMS in the past I have invariably run into problems. I can remember as recently as December 2010 trying to debug a stored procedure on a remote server and it required ports to be opened up on the firewall and other such hoopla – that’s not a pleasant experience when all you want to do is hit F10 and start stepping through your code. Happily in SQL Server 2012 the feature showed up in SQL Server Data Tools (SSDT) which in short is a Visual Studio-based tool aimed squarely at developers rather than DBAs and the advantage this brings is something called LocalDB. LocalDB is quite simply a free, lightweight edition of SQL Server that gets installed with SSDT, lives on your local box, and whose intended use is to help you write code (I do need to blog about LocalDB in more depth at some point). Due to LocalDB being on your local machine debugging T-SQL code there is its as close as its going to get to “it just works”.

Let’s take a look at how this happens, I’m going to demonstrate by debugging stored procedures in an open source project I’m working on called SSIS RestartFramework, the source code (currently) lives at http://ssisrestartframework.codeplex.com/SourceControl/latest in case you want to download it and follow along at home.

As with many features in SSDT the debugging feature is accessed via SQL Server Object Explorer (SSOX). From there one can right click on a stored procedure and select “Debug Procedure…”:

image

Selecting that option displays a dialog where one specifies values for the parameters of the stored procedure:

SNAGHTMLd5abc

Clicking OK launches a query window with some scaffolding code that calls the stored procedure with the specified values:

image

From there one can step through the code, executing each line in turn, using F10 & F11 just as if this were .Net code. Notice how, in the screenshot below, we are stepping through the code of the [RestartFramework].[PutETLJob] stored procedure which we stepped into (using F11) from the scaffolding code above.

image

Furthermore this screenshot depicts the Locals pane displaying the value of all in-scope variables and also the very cool ability to pin a variable (in this case @vRowCount) so that it “floats”, making it easy to see the value and also to see it changing.

I should point out that you do not need to have your stored procedures in LocalDB in order to debug them using SSDT, you can connect to any instance of SQL Server from SSOX and then launch the debugging experience.

This has a been an overview of the T-SQL debugging features in SSDT. Let me know if it proves useful to you.

@Jamiet

Published Thursday, May 30, 2013 9:53 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dave Ballantyne said:

I still say if you see value in a tsql debugger, then you are probably doing tsql wrong.  If it could 'peek' in a table variable or temp table or part process a complex cte then it would be a different story.

May 30, 2013 4:20 PM
 

jamiet said:

Don't understand that point of view at all. Yes, being able to peek at a dataset would be fantastic, but unfortunately we don't have that. Why tho does the absence of that feature mean the ability to see the value of a variable is not useful? Why does it mean I'm "doing it wrong"?

In the example above I'm looking at a value captured from @@rowcount. In my world (ETL) that's a very useful thing to be able to do.

Another thing I'd say is that a lot of people DO do it wrong. I've inherited enough crap code in my life (e.g. massive monolithic sprocs) to know that the ability to step through and find out exactly what's going on in there is very useful indeed.

Buy me a pint tomorrow lunchtime and we'll continue the discussion 😊

May 30, 2013 4:32 PM
 

dave bally said:

Deal :)

May 30, 2013 4:45 PM
 

Mark Wojciechowicz said:

Jamie - thanks for the write-up. I did not notice that in ssox - I prefer the solution explorer and stay out of ssox, but for unit tests.

Good news that they are making some improvements on the SQL debugger.  The coloring was completely awful in ssms, I always lost my place.

I agree with Dave, it's a shame you cannot view tabular data like you could an array or list in .Net.

May 30, 2013 4:46 PM
 

Param said:

A question related to locking of tables & memory utilization. When we debug a SP which is on a remote server, does the memory of the server get utilized or the client? and do the tables get locked?

February 5, 2014 3:14 AM
 

jamiet said:

Hi Param,

Good question, I confess I don't know for sure but would assume that the computation is occurring on the server. As for tables getting locked, I doubt it.

Good questions, wish I could answer them.

regards

Jamie

February 5, 2014 7:56 AM
 

Stored way time out – though glorious when run from SSMS | Zenke said:

November 17, 2014 9:02 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement