current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Looks good, I just realized that ct1 could be done without the case. Let me try. –  OmegaMan Oct 16 '14 at 15:14
    
I liked your second update and used that one. Thanks. –  OmegaMan 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
up vote 1 down vote

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.

share|improve this answer
    
What about the chance of hitting a Null? Does Iff handle them gracefully? –  RubberDuck Oct 17 '14 at 11:43
1  
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. –  OmegaMan Oct 17 '14 at 12:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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