Make an IF to define WHERE

Question

Make an IF to define WHERE

Navigation

#1 by (2 votes)

2

I need a help with a command in a SQL proc.

I basically need something like this:

...
...

WHERE

IF ( @Id_ProductClass IS NULL  )
    WHERE CLIENTPROD.Id_ProductClass NOT IN (59, 150)
ELSE
    WHERE CLIENTPROD.Id_ProductClass = @Id_ProductClass

Is it possible to do something like this?

sql-server procedure

asked by anonymous 20.02.2015 / 17:49

1 answer

Generate Download Page [duplicate] Relate named ID in the same table

score 2 · Answer 1

Include the if condition in the WHERE itself, like this:

WHERE
    (
        (@Id_ProductClass IS NOT NULL AND CLIENTPROD.Id_ProductClass = @Id_ProductClass)
        OR (@Id_ProductClass IS NULL AND CLIENTPROD.Id_ProductClass NOT IN (59, 150))
    )
    -- se não houver mais condições aqui, pode remover os parênteses mais externos.

Or concatenate a string and use the sp_executesql stored procedure to execute it, as did the AP another question .

When you choose to concatenate the query into a string, do not hesitate to use parameters, as suggested in response of that same question.