alert(); function or
console.log I can use to output values when testing and debugging SQL scripts or stored procedures?
Yes, it's called
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.