Equivalent of Debug.Assert for SQL Server

Question

I'm adapting a large number of SQL Server 2005 scripts to merge two of our databases together. The scripts are run from a .cmd file which calls sqlcmd to run the scripts in order. However, I'm experiencing one or two issues where the scripts are failing.

I'd like a quick way to get a look at the state of some of the scripts where they go wrong - check variable values, the results of some queries, stuff like that.

If I was having this problem with a .NET assembly, I'd augment the code with Debug.Assert or set breakpoints where I knew failures were going to occur, which would pause program execution and allow me to check out variable values.

I was wondering, is there an equivalent in SQL Server 2005?

Answer 1

I use batch files and check the error code like this:-

SQLCMD.EXE -b -l 30 -E -S <SERVER> -i "<SQLFILE>.sql">>"%LOG_FILE%"2>&1

IF ERRORLEVEL 1 (
    ECHO. Failed.
) ELSE (
    ECHO. Succeeded.
)

Answer 2

I've never managed to make the integrated debugging work well with SQL Server - I usually resort to "printf" debugging, using either PRINT or RAISERROR statements. RAISERROR can do some basic argument formatting, to spit the values out to the messages window. E.g. if you have a parameter @Val1, of type int, you can do:

RAISERROR('Val1 = %i',10,1,@Val1) WITH NOWAIT

(the WITH NOWAIT option causes the message to appear immediately, rather than the usual SQL behaviour of buffering messages/outputs)