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 
                                 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.

  • 1
    And if there isn't this values on config table, what should select returns? Please try to improve your question by adding at least pseudo-select and desired result. – McNets Jan 31 '18 at 6:46

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})

If your final query returns 2 columns, it returns 2 columns. You can move CASE to the SELECT sentence but it will return 2 columns.

       CASE WHEN @flag1 <> 1 THEN #{COLUMN_VALUE} END AS col2 
WHERE  acct_id = #{accountId};

In case you need only one column named as COL1 or COL2 I'd suggest to use a dynamic query.

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.