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 &gt; 0)
   select @ct2 = 2

if (@ct3 &gt; 0)
   select @ct3 = 3

if (@ct4 &gt; 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.