query parameter

I have to create a stored procedure where the criteria is: "All", specific value and, all except one value

so far this is what I have:

@.Status varchar (50) -- as parameter from a dropdown box

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status ='All'AND @.Status <>'All but closed'THEN'%'WHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.StatusENDand in the storedprocedure.......WHERE Statuslike @.NewStatus

I am a little confused as to how could I return all values except those that have the value "Closed"

Thanks.

in your where clause just add

AND status <> 'closed' or if you had multiple status' status: not in (x, y, z) if that is not helpful, give me a little more info and i will try and help you some more...--jp

|||

Finally I came up with this solution:

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.Statuselse'%'ENDDECLARE @.NewStatusNotvarchar(50)SET @.NewStatusNot =CASE @.StatusWHEN'All but closed'THEN'Closed'else'zz'END.........WHEREStatus.Statuslike @.NewStatusANDStatus.Statusnot like @.NewStatusNot

Not sure if this the best solution but it works

It is the same idea that you have suggested.