CODEWITHSUNDEEP
visual-studio-2010t-sqldebuggingssms
Tahereh Farrokhi
Tahereh Farrokhi

Reputation: 601

how to debug tsql stored procedure?

How do I debug a tsql Stored procedure. I have tried the following link.

http://msdn.microsoft.com/en-us/library/ms241871(v=vs.80).aspx

But I am unable to hit the break point. Is there a better way to debug. My environment is

Sql Express 2008, Visual Studio 2010

Upvotes: 2

Views: 1645

Answers (3)

Dance-Henry
Dance-Henry

Reputation: 953

Debug a stored procedure.

  1. check the logic whether it makes sense or not.
  2. use break point to help find issues.
  3. try to do the modular design as per complex process.
  4. divide the task into multiple simple ones.

  5. use a master stored procedure to take control on the top, and use several child stored procedures to do the job step by step.

  6. As per the optimization, use execution plan, SS Profiler and DTA tools.

Upvotes: 0

Dean
Dean

Reputation: 3346

I have found the debugger in SQL Managment studio unreliable as it's so dependant on having the correct permissions on the db server which are not always available.

One alternate method I use is to convert the stored proc into a long query. I start by moving any parameteres to variable declarations and set their values. For examples the following 

    ALTER PROCEDURE [dbo].[USP_ConvertFinancials] (@EffectiveDate datetime, @UpdatedBy nvarchar(100)) 
AS
BEGIN

    DECLARE @PreviousBusinessDay datetime

would become 

DECLARE @Value int, 
        , @EffectiveDate datetime = '01-Jan-2011
        , @UpdatedBy nvarchar(100) = 'System'

This allows me to run the queries within the stored procedure starting from the top. As I move down through the queries, I can check the values of variables by simply selecting them and rerunning the query from the top:

SELECT @Value

I can also comment out the INSERT portion of INSERT-SELECT statements to see what is being inserted into tables and table variables.

The bug in the stored proc usually becomes quite evident using this method. Once I get the query running correctly I can simply copy the code to my proc and recompile.

Good luck!

Upvotes: 2

sll
sll

Reputation: 62504

You can try out Sql Profiler, it does not allows a classical debugging like "break at this point" but gives you an information in great detail about what is going on on each step of a query/SP execution.

Unfortunately Microsoft does not provide it with Express Edition version of Sql Server. BUT :) There is a good (relatively because it does not provide a lot of filtering criterias which exists in Microsoft's one) and free alternative - SQL Server 2005/2008 Express Profiler.

Upvotes: 0

Related Questions