Creating stored procedure having different WHERE clause on different search criteria without putting all in long string.

Question

Is there any alternate way to create stored procedure without putting all query in one long string if criteria of WWHERE clause can be different.

Suppose I have Orders table I want to create stored procedure on this table and there are three column on which I wnat to filter records.

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

If user only give CustomerId in search criteria then query should be like following

SELECT * FROM Orders WHERE Orders.CustomerId = @customerId

And if user only give ProductId in search criteria then query should be like following

SELECT * FROM Orders WHERE Orders.ProductId = @productId

And if user only all three CustomerId, ProductId, and SupplierId is given then all three Ids will be used in WHERE to filter.

There is also chance that user don't want to filter record then query should be like following

SELCT * FROM Orders

Whenever I have to create this kind of procedure I put all this in string and use IF conditions to check if arguments (@customeId or @supplierId etc) has values.

I use following method to create 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 could pass NULL for fields that you don't 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 )

Don't use SELECT *, always list the columns you actually need.

Answer 2

HI,

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 pair of articles, describing various search techniques and trade offs (one for SQL 2008, one for earlier versions)