My colleague created this SQL to handle certain user selections which are based on an on/off bit flag. The goal is to identify those individual bit flags with a specific integer which will be used in a later SQL to group specific types which the flags relate to.

What would be a more elegant way of doing this on SQL-Server 2012 which would remove the proceeding if statements?

DECLARE @ct1 int;
DECLARE @ct2 int;
DECLARE @ct3 int;
DECLARE @ct4 int;

SELECT  @ct1 = ISNULL(TypeDaily, 0) ,
        @ct2 = ISNULL(TypeTerm, 0) ,
        @ct3 = ISNULL(TypePerformance, 0) ,
        @ct4 = ISNULL(TypeWeather, 0) 
FROM    ReportTable
WHERE   ReportSelectionId = @ReportSelectionId;

if (@ct2 > 0)
   select @ct2 = 2

if (@ct3 > 0)
   select @ct3 = 3

if (@ct4 > 0)
   select @ct4 = 4

The Type.... fields above are valid bit columns on the ReportTable table.


This looks like a good candidate for a 'case' selection statement:

SELECT  @ct1 = case when TypeDaily = 1 then 1 else 0 end,
        @ct2 = case when TypeTerm = 1 then 2 else 0 end,
        @ct3 = case when TypePerformance = 1 then 3 else 0 end,
        @ct4 = case when TypeWeather = 1 then 4 else 0 end,
FROM    ReportTable
WHERE   ReportSelectionId = @ReportSelectionId;

Note, you seem to have an extra , at the end of your selection list.

Using the above system there is no need for the if-conditions.

An alternate syntax which reduces the process to arithmetic, would be:

SELECT  @ct1 = 1 * isNull(TypeDaily,0),
        @ct2 = 2 * isNull(TypeTerm, 0),
        @ct3 = 3 * isNull(TypePerformance,0),
        @ct4 = 4 * isNull(TypeWeather,0),
FROM    ReportTable
WHERE   ReportSelectionId = @ReportSelectionId;

In the past (before the case statement was available) I used to do calculations like the above, but depending on the circumstances, it can be less readable than the case. In this instance, I think the case is still better.

  • \$\begingroup\$ Looks good, I just realized that ct1 could be done without the case. Let me try. \$\endgroup\$ – ΩmegaMan Oct 16 '14 at 15:14
  • \$\begingroup\$ I liked your second update and used that one. Thanks. \$\endgroup\$ – ΩmegaMan Oct 16 '14 at 15:35
  • \$\begingroup\$ I like the second one too. The first neglects NULL. \$\endgroup\$ – RubberDuck Oct 17 '14 at 11:42
  • \$\begingroup\$ @RubberDuck - the first one does not neglect NULL. a null value successfully becomes 0 as part of the case because NULL = 1 is false, so the else 0 happens \$\endgroup\$ – rolfl Oct 17 '14 at 12:16

SQL Server 2012 introduced IIF which is basically an alternate CASE syntax that you might find easier to use. The IIF in SQL-Server works much like the function of the same name in Excel.

The IIF syntax is:

IIF ( boolean_expression, true_value, false_value )

And these are the arguments it takes:

A valid Boolean expression. If this argument is not a Boolean expression, then a syntax error is raised.

Value to return if boolean_expression evaluates to true.

Value to return if boolean_expression evaluates to false.

Here is the query using IIF:


SELECT  @ct1 = IIF(TypeDaily = 1, 1, 0),
        @ct2 = IIF(TypeTerm = 1, 2, 0),
        @ct3 = IIF(TypePerformance = 1, 3, 0),
        @ct4 = IIF(TypeWeather=1, 4, 0)
FROM    ReportTable
WHERE   ReportSelectionId = @ReportSelectionId;

I also took the liberty of changing all keywords into ANGRYCASE which is the conventional way that T-SQL is written. I would personally recommend using either ALL CAPS or all lower, whichever you find more comfortable. Just try to make your formatting consistent.

  • \$\begingroup\$ What about the chance of hitting a Null? Does Iff handle them gracefully? \$\endgroup\$ – RubberDuck Oct 17 '14 at 11:43
  • 1
    \$\begingroup\$ This will basically do the same as the CASE query provided by rofl. I just wanted to make you aware of the alternate syntax. Taking the first IIF, what it does is check if TypeDaily is equal to 1, if it is, then 1 will be returned, otherwise 0 will be returned. This means 0 is returned for NULL values. \$\endgroup\$ – PenutReaper Oct 17 '14 at 11:51
  • \$\begingroup\$ There is always more than one way to do something and this solution is just as graceful and the other one. Thanks for taking the time, I consider this answer just as valid as the other. \$\endgroup\$ – ΩmegaMan Oct 17 '14 at 12:10

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.