SQL Query - Optional "where" parametres
There is a really simple way you can add optional parameters to a SQL select statement without having to convert the query into TSQL or any other dynamic way to create the query. The query uses the "CASE" clause to perform the required task. Here's how:
--Normal Select Query (It has a @Customer param)
Select
C.CustomerID,
C.CustomerName,
C.Addr1,
C.Addr2,
C.Addr3,
C.Postcode
From Customer C
--Lets use a Case statement to see if the @Customer parameter in the Query has something in it
where
C.Customer =
Case
--If @Customer's length is greater than 0 then filter
When Len(@Customer) > 0 then @Customer
--Dont use the filter
Else C.Customer
End
--Return to standard SQL call
Order by C.Customer asc
You can do this more than once. It's also really helpful when adding this as a stored proceedure into Visual Studio for LINQ queries as some TSQL doesn't get read properly.