Hi,

I have the below initialization

DECLARE @Active bit =1 ;
Declare @id int

SELECT @Active=CASE WHEN id=@id and [Rank] ='Good' then 0 else 1 END  FROM dbo.Students

I have to change this query in such a way that the conditions id=@id and [Rank] ='Good' should go to the where condition of the query. In that case, how can i use Case statement to retrieve 1 or 0? Can you please help me to modify this initialization?

• Edited by vskindia 11 hours 29 minutes ago simplification

Why do you want CASE ...when here? I you want to retrieve the records for Rank =@rank the below would do. 

SELECT *  FROM dbo.Students where Rank=@rank --This would be enough

with case ...when

SELECT *  FROM dbo.Students where  0=CASE WHEN [rank] =@Rank then 0 else 1 END

thanks - I have modified the question a little bit- I don't want the rank, I want 1 or 0 only. Can you please help me?

• Edited by vskindia 11 hours 19 minutes ago edit

if you want to assign 0 or 1 then the below query would do:

DECLARE @Active bit =1 ;
Declare @id int

SELECT @Active=CASE WHEN id=@id and rank ='Good' then 0 else 1 END  FROM dbo.students

Select @Active

SELECT CASE WHEN id=@id and rank ='Good' then 0 else 1 END,*  FROM dbo.students where @Active=CASE WHEN id=@id and rank ='Good' then 0 else 1 END

thanks - i don't want the CASE WHEN id=@id and rank ='Good' in the select statement...i want to move this to where condition because its a performance issue because the table has 5lacks records...so thats y i need a fine tuned query..

thanks in advance!

I dont understand your query...May be below? or provide us sample data and your output...

SELECT *  FROM dbo.students

where @Active=CASE WHEN id=@id and rank ='Good' then 0 else 1 END

But, I doubt you will have performance improvement here?

Do you have index on id?

If you are looking for getting the data for @ID with rank ='Good' then use the below:Make sure, you have index on id,rank combination.

SELECT *  FROM dbo.students

where  id=@id and rank ='Good' 

• Edited by Latheesh NKMicrosoft community contributor 10 hours 50 minutes ago

>> CASE WHEN id=@id and rank ='Good' in the select statement...i

In that case you have to use case statement.

>>.so thats y i need a fine tuned query..

Why you want to tune the query? at present is there a performance problem?Please post the execution plan

I think you have to explain your desired result a bit better.

The query in your question returns only one result. The value of @Active will be determined by the last row that the engine happens to process.

Do you want a 0 or 1 for each row in the table?

Because if you only want information on a subset of all rows, then you would need a WHERE clause that restricts the selection to that subset.

The questions above determine whether the entire table needs to be processed or not, and because of that if the performance can be improved.