Working with Stored Procedures and functions on a database tier can be time consuming, hard to debug and sometimes just difficult to get clarity on what is “happening”. A rarely discussed feature of Microsoft SQL Management Studio is its inbuilt debugging features. These allow you to see exactly what is going on, and step through your logic in a similar fashion to working in Visual Studio.I have spent a fair bit of time in SQL Management Studio of late, working on a legacy project at my new workplace. You can only imagine my sheer *delight* that nearly all the logic for the application in discussion is done on the database tier, using stored procedures, triggers and CLR functions. Just magic… (if you haven’t picked up the sarcasm yet, here is a prod…).
Because of my recent sustained trip to the land of pain, i have quickly had to work on my SQL fu to be able to solve problems and get to the bottom of their cause as quickly and efficiently as possible. Writing a whole bunch of print @MyVariableName to the screen just doesn’t cut it with complicated bugs. SQL Management Studio’s debugging tool helps reduce the time needed for this greatly.
End the Pain gameFor the example i want to show you I'm going to walk you through a number of scenarios. I’m going to debug a bit of SQL that executes a stored procedure, and within that stored procedure I'll call a SQL function. The reason for this example being slightly abstracted is so that you can actually gather the power of this tool for working with complicated scenarios. I won’t show you, but the same techniques can be used to following the path of a Trigger after the event you have just fired happens.
Optionally you can avoid doing any of the below and simply pick up “how” i debug and apply it to a database you know and *maybe* love.
Demo SetupOpen SQL Management Studio and create a blank database.
Run the SQL script below in a new query window
Read more: Diary of Ninja...
Posts
0 comments:
Post a Comment