Bagong Bagong - 3 months ago 7
SQL Question

How to use case or if with in Where clause in SQL SERVER

How to use case or if with in Where clause in SQL SERVER?

DECLARE
@Sta int
Set @Sta = 1

SELECT TOP 1000
[No]
,[Material]
,[Material Description]
,[Procurement Type]

FROM [PMLite].[dbo].[zSTO:8/22/2016]
WHERE IF @Sta = 1
[Procurement Type] IN ('F','E')
ELSE @Sta = 2
[Procurement Type]= 'F'


Can anyone help for this case..

Answer

You can't use if or case statements in where clause in the manner you're trying to do it.

But in fact you don't need these statements since your condition can be rewritten using logical operators as:

 WHERE (@Sta = 1 and [Procurement Type] IN ('F','E'))
        or (@Sta = 2 and [Procurement Type]= 'F')