double case statement

Try nesting the case statements.... what you're trying works in later versions of SQL, not familiar with 2005 enough to say if this was added later. the below should work though.

case when state=@stateAb THEN CASE WHEN usedForCity=0 THEN 0 ELSE 1 END ELSE 1 END

Select allOpen in new window

>when I try to do this, I don't get it returned state = only
Offhand the syntax appears correct, assuming state and @stateAb does not contain a NULL value.  If there are NULLs, then you'll have to use the ISNULL function to handle them.   Can you post your full T-SQL?

In case it helps, an example of nesting CASE statements is in SQL Server CASE Solutions, do a find on 'CASE blocks can also be nested within themselves'.

>use to prioritize the ordering.
Also, the section below that deals with CASE in an ORDER BY clause.

select  top 100 percent * into #t1 from k4kReviewsForSite2013 ORDER BY
   -- First sort position
 CASE WHEN state = @stateAb THEN 0 ELSE 1 END
 
 select row_number() over( order by (select 1)) rn, *  into #t2
from #T1 

--select * from #t1
--select * from #t2

select * from #T2 where rn between @startNum and @endNum --order by idforstate

Select allOpen in new window

when I write to the temporary table  it works the first way.
when when I add the second clause, i lose the correct ordering.

Define 'the correct ordering'.

right ordering is only if state still shows up first.

Perhaps you could post some sample data and your expected results to help clarify your intent for us?