Debugging Stored Procedure in SQL Server 2008

Question

Is there any way to debug a stored procedure on SQL Server 2008?

I have access to use SQL Server Management Studio 2008 and Visual Studio 2008 (not sure whether either provides this functionality).

Generally I use the SQL profiler to find the parameters passed to the stored proc, however would like to be able to step through the procedure to see where it is failing.

Is this possible?

What is the best way? (in terms of quickly finding location of bugs)

Answer 1

Well the answer was sitting right in front of me the whole time.

In SQL Server Management Studio 2008 there is a Debug button in the toolbar. Set a break point in a query window to step through.

I dismissed this functionality at the beginning because I didn't think of stepping INTO the stored procedure, which you can do with ease.

SSMS basically does what FinnNK mentioned with the MSDN walkthrough but automatically.

So easy! Thanks for your help FinnNK.

Edit: I should add a step in there to find the stored procedure call with parameters I used SQL Profiler on my database.

Answer 2

Yes you can (provided you have at least the professional version of visual studio), although it requires a little setting up once you've done this it's not much different from debugging code. MSDN has a basic walkthrough.