Creating stored procedure having different WHERE clause on different search criteria without putting
- by Muhammad Kashif Nadeem
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.
