当前位置: 动力学知识库 > 问答 > 编程问答 >

tsql - Debug output in T-SQL

问题描述:

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?

网友答案:

Yes, it's called PRINT. See here:

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

网友答案:

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
网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: