Sql-server – Dynamic select column name on the basis of a flag

sql serversql-server-2008

I have a config table where there are config values are stored as key, value pair in each row.
And there is another table which have two columns (lets say column1 and column2). Now I have to select column1 value in select statement if value for flag1 is present and equal to 1 in config table and select column2 if value for flag1 is absent or equal to 0.

I fetched the flag value in a variable- 

DECLARE @flag smallint 
SELECT @flag = CASE WHEN EXISTS (SELECT 1 
                                 FROM   dbo.ACCOUNT_CONFIG WITH (nolock) 
                                 WHERE acct_id = #{accountId} 
                                 AND key_name = 'flag1' AND key_value = 1) THEN 1 
                                 ELSE 0 END

Now I have to do some thing like:

SELECT col1, col2 
From   ACCOUNT_USER table 
where  acct_id = #{accountId} 
AND    CASE @flag1 = 1 THEN column1 = #{COLUMN_VALUE} 
       ELSE column2=#{COLUMN_VALUE} END

But above statement is not working for me.

Best Answer

The problem is that you are using conditions as expressions in your second query, i.e. inside a CASE expression after THEN and after ELSE. Boolean/logical expressions cannot be used like that in Transact-SQL. You can only use them as conditions in contexts where conditions are expected. In a Searched CASE expression, for instance, a condition can only go after the WHEN keyword.

The resolution of the issue you are facing depends on whether each of the two columns needs to be compared to the same value or to a different one.

If it is the same value, then you can move the = #{COLUMN_VALUE} part outside your CASE expression:

...
WHERE  acct_id = #{accountId} 
AND    CASE WHEN @flag1 = 1 THEN column1 ELSE column2 END = #{COLUMN_VALUE}

This way the CASE expression simply returns either one column or the other, and the result is compared to the specified value.

If each column is supposed to be matched against its own value, the solution will be different. You do not need a CASE expression at all. Instead, you need two pairs of conditions OR-ed together:

...
WHERE  acct_id = #{accountId} 
AND    (@flag1 =  1 AND column1 = #{COLUMN_VALUE}
     OR @flag1 <> 1 AND column2 = #{COLUMN_VALUE})
Related Question