4
\$\begingroup\$

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?

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

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); 
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.