December 10, 2008 at 4:57 am
[font="Comic Sans MS"]How to debug a stored procedure in SQL 2005?[/font]
December 10, 2008 at 5:11 am
It seems it is not available in SQL 2005. It is available in 2008 though. Please see this link.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3909125&SiteID=1
December 10, 2008 at 5:43 am
Like most items in SQL Server the answer is "It depends".
a. Is the SP reporting an error?
b. Is the SP returning invalid / incomplete data?
Using SSMS
In either case use the Print statement to print the value of passed parameters (if any).
Periodically insert Print statements to show progress through the SP.
If a long complex SP and it is reporting an error simplify the SP and gradually add complexity.
If the SP uses a sub query, check what is returned by the sub query.
If it is taking too long to return results run show plan to determine if it is performing table scans rather that using indexes.
Of course all of the above should be performed on a development server and NOT a production server.
December 10, 2008 at 7:52 am
Execution plans are your friend.
They will show what the query is doing inside the engine. From there you can determine if you need indexes etc. If the data is not correct, you'll have to check your logic, which is a different issue.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply