When debugging T-SQL, is there something like Javascript's alert(); function or console.log I can use to output values when testing and debugging SQL scripts or stored procedures?
-
4Print statement ? or just write a select statement to look at thge values in temp table variablesCharles Bretana– Charles Bretana2011-11-13 00:58:05 +00:00Commented Nov 13, 2011 at 0:58
Add a comment
|
4 Answers
Yes, it's called PRINT. See here:
1 Comment
silentAmbassador
I needed something like print statement to figure out why or when my CTE went into infinite loop.
You can use PRINT to output messages to log but I personally prefer using RAISERROR with low severity because PRINT output is not always printed to screen immediately. Especially in long runnig SP's. On the other hand something like this prints immediately:
RAISERROR ('My Message', 10, 1)
Do not be fooled by the name ERROR, an error with severity 10 does no harm.
As for CTE, I think you are having a problem with self referencing Common Table Expressions. But you should know that there is no way to insert a PRINT statement into the execution of a recursive CTE.
To debug those I usually add an extra "Iteration Index" column to the result set that is incremented with each iteration. Also some other columns to help assess the situation. When examined with the rest of the resultset, it usually gives good insight. Something like the IDX col down. Other columns help me examine join conditions to see what was wrong:
WITH x AS (
SELECT 1 AS IDX, A._ID as ID_A, NULL as ID_B
FROM MYTABLE A
WHERE A._ID = 6
UNION ALL
SELECT X.IDX + 1, A._ID, B._ID
FROM MYTABLE B INNER JOIN X ON B._ID = X._ID + 1
WHERE B._ID < 20
)
SELECT *
FROM X
Comments
I use the instruction "RAISERROR" with the hint "WITH NOWAIT" like below:
RAISERROR('My message here', 10, 1) WITH NOWAIT
This instruction displays the message in time.
Comments
PRINT statement helps. Instead of PRINT giving output to result pane I sometimes insert the debug output to a table.
Newer version of SQL (2008 and 2008R2) has debug option. Not as solid as debugging a c# project in Visual Studio but quite good. You can go step by step and also create variable watchlist.
