Is it possible to use IF ELSE in the middle of WHERE Clause of StoredProcedure

Question

Hi MSDN,

Is it possible to use condition (if-else) in the middle of WHERE Clause of StoredProcedure ?

i.e.

@refNo int , @targetNo int
AS
SELECT * FROM Batch WHERE BatchNo = @targetNo
     IF @refNo > 10
           AND BatchNo = @refNo
     ELSE
           AND  BatchNo < @refNo

Please give me some advice

Thank you

SweNz

ps. SQL SERVER 2005

Answer 1

@refNo int , @targetNo int
AS
SELECT * FROM Batch
WHERE BatchNo = @targetNo
AND ((@refNo > 10 AND BatchNo = @refNo)
 OR  BatchNo < @refNo)

Answer 2

For conditional where use Case expression

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 3

Try this:

SELECT * FROM Batch
WHERE BatchNo = @targetNo AND ((@refNo > 10 AND BatchNo = @refNo) OR (@refNo <= 10 AND BatchNo < @refNo))

David.

Answer 4

Thanks a lot David , Rishabh  , oldjeep

I got and idea and will try tomorrow (midnight here)

goodnight then :)