Debug a stored procedure

hello,
I want to know if it is possible to debug a stored procedure using visual studio 2003 and SQL server 2000.
if yes can u help me how to do this?

Thanks.

It is possible to turn on SQL debugging, however there is a simpler means:

Add an additonal argument to the stored procedure arguments like this:

ALTER PROCEDURE dbo.BlahBlah
(
@.DEBUG Bit = 0
)
AS
SET NOCOUNT ON
IF @.DEBUG = 1
PRINT 'Debug display'
RETURN

You can then run the stored procedure within query analyser with

EXEC BlahBlah 1

|||

Thanks for your reply, but I didn't understand your example,
Could you explain more what do u mean.

What about "It is possible to turn on SQL debugging" how can i do this??
thanks.

|||

I am running on VS2005 not VS2003 on my current machine, hence I cannot give you the step the turn SQL debugging within VS2003. However when I was using VS2003 and SQL2000, I found it very much simpler to debug within query analyser using print statements. If you condition the print statement as in the example, then you can leave them in place in production. You may have I had once a 2000 line s.p. to debug, you could follow the flow by adding at say line

IF @.DEBUG = 1 PRINT 'Line 100'

If you declare a variable like

DECLARE @.PRINTLINE VARCHAR(150)

you can then display the value of @.FRED by

IF @.DEBUG = 1 BEGIN
SELECT @.PRINTLINE = 'FRED=' + CONVERT(VARCHAR(20), @.FRED)
PRINT @.PRINTLINE
END

|||That's a good idea to use PRINT for debugging. However I'd rather using SQL Profiler to capture a trace when the stored procedure is being executed, as from the trace I can see what's happening to SQL when the stored procedure is executed.|||

SQL Profiler is very good at identifying performance bottlenecks however for tracking the value of variable within a long stored procedure, a series of print statements becomes very useful. In once particular case, I had a stored procedure that was about 2000 lines long and a particular variable was getting set to null. I used some TSQL like:

IF @.DEBUG = 1 BEGIN

IF @.VAR IS NULL PRINT 'Step 12A @.VAR IS Null'

END

Once in place, I was able to rapidly locate the point at which the null value was being generated and fix the problem.