Printing simple SQL comparsion

Question

I'm comparing two variables which I'd like to be printed to the console like so:

==Full Ex Vat==
true

Currently the code to do this is:

print '==Full Ex Vat=='
print case when
@aTotalExVat = @sTotalExVat
then 'true' else 'false' end

which I feel isn't as elegant as it could be.

Any tips?

• janos: Seems fine to me. Not sure what bugs you with this. I'd write the second print statement on a single line though.
• m.edmondson: I have a few of these. I'm looking for a way to tidy up and hopefully be able to reuse.
• janos: To make it more reusable, maybe you want to put in a stored proc

Answer 1

SQL uses three valued logic.

When one of the operands is null the expression evaluates to UNKNOWN not false. Your current code does not take account of this and can be altered as below to do so.

PRINT CASE
 WHEN @aTotalExVat = @sTotalExVat THEN 'true'
 WHEN @aTotalExVat <> @sTotalExVat THEN 'false'
 ELSE 'unknown'
 END 

If you wanted to treat NULL = NULL as true and NULL = any not null value as false you could use

DECLARE @Message VARCHAR(5) = CASE WHEN EXISTS(SELECT @aTotalExVat 
 INTERSECT 
 SELECT @sTotalExVat) 
 THEN 'true'
 ELSE 'false'
 END;
PRINT '==Full Ex Vat==';
PRINT @Message; 

On SQL Server 2012 there are a couple of new functions that shorten the above somewhat

DECLARE @Message VARCHAR(5) = IIF(EXISTS(SELECT @aTotalExVat 
 INTERSECT 
 SELECT @sTotalExVat), 
 'true', 
 'false');
PRINT CONCAT('==Full Ex Vat==', CHAR(13), ( CHAR(10) ), @Message);