Variable parameters for the where claues of a stored proc
-
I have a stored procedure where some of the input parameters may not have values. At the moment I'm writing 2 Select statements and using an IF test to determin which to use, as shown below.
Now I'm sure there has to be a better way of doing this, but I can't see it. Any ideas please?
IF @WeekNo = 9999
SELECT etc'
WHERE (dbo.Orders.Type = 0) AND (dbo.Orders.OrderID BETWEEN @FromSalesOrderID AND @ToSalesOrderID)
Else
SELECT etc'
WHERE (dbo.Orders.Type = 0) AND (dbo.Orders.WeekNo = @WeekNo)
Many thanks
Tim
Tim -
Dynamic Search Conditions in T-SQL
You could do something like this :
Select * from dbo.Orders O where O.Type = 0
And (@FromSalesOrderID IS NULL OR O.OrderID Bewteen @FromSalesOrderID AND @ToSalesOrderID)
AND (O.WeekNo IS NULL or O.WeekNo = @WeekNo)
you just have to make sure that at least one param is set or you'll return the whole table.
-
Thank you, that worked a beautifully (with a slight change of the code).
And yes the calling code will always ensure taht at least one param is set.
Many thanks
Tim
Tim -
HTH.
-
You could try something like:
Select * from dbo.[YourTable] where
(@p='' or Colp1 = @p1)
and
(@p2='' of Colp2 = @p2)
and
(...)
and
@p1 + @p2 +... <>''
then there's that little problem with indices...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply