Creating a stored procedure with a different WHERE clause according to different search criteria, without putting everything in a long string

Question

Is there an alternative way to create a stored procedure without putting the entire request in one long line if the criteria for the WWHERE clause can be different.

Suppose I have a table of orders. I want to create a stored procedure in this table, and there are three columns on which I will filter the records.

1- CustomerId, 2- SupplierId, 3- ProductId.

If the user provided CustomerId only in the search criteria, the request should look like this

SELECT * FROM Orders WHERE Orders.CustomerId = @customerId 

And if the user only gives ProductId in the search criteria, then the query should look like this

 SELECT * FROM Orders WHERE Orders.ProductId = @productId 

And if the user is provided with only all three CustomerId, ProductId and SupplierId, then all three identifiers will be used in WHERE for filtering.

There is also a chance that the user will not want to filter the record, then the query should look like this:

 SELCT * FROM Orders 

Whenever I have to create such a procedure, I put all this in a string and use the IF conditions to check the argument values ​​(@customeId or @supplierId, etc.).

I use the following method to create a procedure

 DECLARE @query VARCHAR(MAX) DECLARE @queryWhere VARCHAR(MAX) SET @query = @query + 'SELECT * FROM Orders ' IF (@originationNumber IS NOT NULL) BEGIN BEGIN SET @queryWhere =@queryWhere + ' Orders.CustomerId = ' + CONVERT(VARCHAR(100),@customerId) END END IF(@queryWhere <> '') BEGIN SET @query = @query+' WHERE ' + @queryWhere END EXEC (@query) 

Thanks.

Answer 1

You can pass NULL for fields that you do not want to include in your WHERE -clause, and check for NULL in the query:

 Select customerId, productId, supplierId From Orders Where ( customerId = @customerId Or @customerId IS NULL ) And ( productId = @productId Or @productId IS NULL ) And ( supplierId= @supplierId Or @supplierId IS NULL ) 

Do not use SELECT * , always specify the columns you need.

Answer 2

 Select CustomerId, ProductId, SupplierId From Orders Where CustomerId = ISNULL( @customerId, CustomerId ) And ProductId = ISNULL( @productId, ProductId ) And SupplierId= ISNULL( @supplierId, SupplierId ) 

Answer 3

Erland Sommarskog has a great couple of articles describing various search methods and trade-offs (one for SQL 2008, one for earlier versions)