CODEWITHSUNDEEP
sqlsql-server-2008case
hunt
hunt

Reputation: 321

sql case statement in where?

select t1.customer_code as doc_num, CONVERT(VARCHAR,t2.created_on,103) as doc_date,
t2.sap_cardcode as sap_doc_num ,t2.void_flg,t2.status_ind,t2.err_msg
from customer t1
inner join sap_customer t2 on t1.id = t2.customer_id
where t2.status_ind = case when @test = 'todo' then t2.status_ind='1' else t2.status_ind='0' END

Above is the select statement that i need to do, based on the where statement i need to pass a param to determine which want should it execute. Status Ind = 1 or Status Ind = 0

Upvotes: 0

Views: 49

Answers (2)

Sujith
Sujith

Reputation: 1784

I have modified the Case part a little in your query. Hope this helps:

select t1.customer_code as doc_num, CONVERT(VARCHAR,t2.created_on,103) as doc_date,t2.sap_cardcode as sap_doc_num ,t2.void_flg,t2.status_ind,t2.err_msg
from customer t1
inner join sap_customer t2 on t1.id = t2.customer_id
where t2.status_ind = case when @test = 'todo' then '1' else '0' END

OR you can use case like below:

where t2.status_ind = case @test when 'todo' then '1' else '0' END

Upvotes: 3

Ravi
Ravi

Reputation: 31417

Your CASE statement needs to be modified

case when @test = 'todo' then '1' else '0' END

Since, you are going to compare where t2.status_ind. So, you need to return some value from CASE instead of setting it.

Upvotes: 2

Related Questions