Debug output in T-SQL

Question

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?

Answer 1

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

Answer 2

Yes, it's called PRINT. See here:

http://msdn.microsoft.com/en-us/library/ms176047.aspx