The Art of Debugging – A Developer’s Best Friend – Lesson 11 – SQL Server Stored Procedures
This section of my blog is to illustrate how visual studio can be used to debug stored procedures in SQL Server. Luckily, most of the commands are identical and the learning curve is trivial. Let's get started.
We will use Server Explorer to attach to AdventureWorks.
In this blog we will add a new stored procedure. We will execute the stored procedure. We will step through the stored procedure and use traditional debugger commands that have been discussed in the previous 10 blogs.
These are very powerful techniques and are dramatic time savers. You can also use these techniques to help you discover how other store procedures were written by other developers.
Begin by going to Visual Studio's View menu and selecting Server Explorer.
Right mouse click on Data Connections and select Add Connection.
Notice that our AdventureWorks database is already contained or hosted by SQL server 2008. This is the instance that we wish to connect to and debug.
Click on the change button and make sure you select Microsoft SQL server as the data source type.
When I tried to connect directly to the SQL server database file, the .mdf file, I got an error indicating versioning problem.
I recommend connecting to the database instance posted within SQL server.
This is a more realistic scenario anyway.
After entering localhost for the server name, you'll see a drop down list of the available data bases. Select the AdventureWorks database and click OK.
In this example we will add to a stored procedures.
The first store procedure will contain some simple selects.
The second stored procedure will contain some business logic allowing us to test the debugger more complex scenario
Name of stored procedure = uspGetList
Download the stored procedure here - sp_uspGetList.sql
Within server explorer right mouse click and stored procedures and add new stored procedure.
Paste in the stored procedure code then select file save.
Notice the stored procedure appears in the server explorer to the left, highlighted in blue.
The first select statement chooses those products whose list price is less than the maximum price.
The second select statement chooses the maximum list price for a given product that is less than the maximum price passed into the stored procedure.
Running [Production].[uspGetList] ( @Product = %Frame%, @MaxPrice = 500, @ComparePrice = 350, @ListPrice = 400 ).
To start the debugging session, right mouse click on the stored procedure and Select Step Into Stored Procedure.
You will now be able to use the F10 and F11 keys to step through your stored procedure code.
A dialog box will appear that allows you to provide parameters for your stored procedure.
Notice the yellow arrow indicates the current line of execution.
You can hover the mouse over variable names to determine their value.
Notice that I added a little if statement.
Many of the same techniques that I've discussed in the previous 10 blogs apply debugging SQL server stored procedures.
Notice that the locals window is there, the output window is there.
This concludes the tutorial on using Visual Studio 2008 to debug of SQL Server Stored Procedures.
Published Monday, February 09, 2009 9:47 AM by BrunoTerkaly
.jpg)
No comments:
Post a Comment