# Assign Specific Value If BitField Is True

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.

• Looks good, I just realized that ct1 could be done without the case. Let me try. – ΩmegaMan Oct 16 '14 at 15:14
• I liked your second update and used that one. Thanks. – ΩmegaMan Oct 16 '14 at 15:35
• I like the second one too. The first neglects NULL. – RubberDuck Oct 17 '14 at 11:42
• @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 – 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:

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

true_value
Value to return if boolean_expression evaluates to true.

false_value
Value to return if boolean_expression evaluates to false.

Here is the query using IIF:

DECLARE @ct1 INT;
DECLARE @ct2 INT;
DECLARE @ct3 INT;
DECLARE @ct4 INT;

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.

• What about the chance of hitting a Null? Does Iff handle them gracefully? – RubberDuck Oct 17 '14 at 11:43
• 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. – PenutReaper Oct 17 '14 at 11:51
• 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. – ΩmegaMan Oct 17 '14 at 12:10