how to debug tsql stored procedure?

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

ht开发者_如何转开发tp://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

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!

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.

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.