CODEWITHSUNDEEP
sqlsql-server
Slee
Slee

Reputation: 28248

CASE in SELECT WHERE statement for stored procedure?

I need to filter a table based on ManufacturerID in a stored procedure BUT when a null ManufacturerID is passed in I need all orders.

Can I do this in the WHERE statement so I don;t have to have the entire query written twice in my SP?

Upvotes: 3

Views: 369

Answers (4)

sam yi
sam yi

Reputation: 4934

This can also lead to parameter sniffing issues. If possible, might want to clean up the nulls first instead?

Upvotes: 1

user359040
user359040

Reputation:

Try:

WHERE table.field = coalesce(@param, table.field)

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can do this:

where t.ManufacturerID = @ManufacturerIDParam OR @ManufacturerIDParam is null

Upvotes: 2

MatBailie
MatBailie

Reputation: 86706

WHERE
  @param IS NULL OR field = @param

But note, this gets very inefficient if you scale it up to search multiple columns. (See http://www.sommarskog.se/dyn-search.html)

Example of inefficient scaled up version...

WHERE
      (@param1 IS NULL OR field1 = @param1)
  AND (@param2 IS NULL OR field2 = @param2)
  AND (@param3 IS NULL OR field3 = @param3)

Upvotes: 6

Related Questions