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'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)

Answer 2

This will work:

-- Assert procedure equivalent to other languages.
-- raiserror() will cause sql execution to stop and throw execep in C# code that is running this statement.
-- Usage:
-- declare @shouldBeTrue bit
-- set @shouldBeTrue = case when 1=0 then 1 else 0 end
-- exec _AT3Assert @shouldBeTrue, 'failed'
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = '_AT3Assert' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
 EXEC ('DROP PROCEDURE dbo._AT3Assert')
 GO
create procedure dbo._AT3Assert
    @shouldBeTrue bit,      
    @errorMsg nvarchar (max)
AS
    SET NOCOUNT ON;
    if @shouldBeTrue is null or @shouldBeTrue <> 1
    begin
        raiserror (@errorMsg, -- Message text.
               11, -- Severity.
               1 -- State.
               );
    end
GO