Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    23

    Unanswered: stored procedure parameter filtering

    consider a stored procedure with a parameter @OrderID, i want to perform the following query :
    select * from Orders where OrderID = @OrderID

    i want the condition to be true when parameter @OrderId is null so what is the syntax for that? i think there is an IF CONDITION that can be embedded with where clause.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT <define columns, don't use *>
    FROM   orders
    WHERE  orderID = @OrderID
    OR     orderID IS NULL
    Also, read this: http://weblogs.sqlteam.com/jeffs/arc...e-clauses.aspx
    Different methods suggested over there with reasoning to chose too
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by akkad
    i want the condition to be true when parameter @OrderId is null so what is the syntax for that? i think there is an IF CONDITION that can be embedded with where clause.

  4. #4
    Join Date
    Jan 2008
    Posts
    23
    u r laughing ha , ok here is my case, am not using direct select, am executing a string but am having the following error:
    An expression of non-boolean type specified in a context where a condition is expected, near 'Reques'.

    here is the query :


    ALTER procedure [dbo].[GetFilteredRequests]
    (
    @Product nvarchar(500),
    @Status nvarchar(500)
    )
    as
    set nocount on

    DECLARE @strSql nvarchar(500)

    SET @strSql = '
    select Requests.ReqNo, Requests.Title, Products.Name, Requests.CreateDate, datediff(day, Requests.UpdateDate, getdate()) as LastUpdAge,
    datediff(day, Requests.CreateDate, getdate()) as Age, datediff(day, Requests.CreateDate, Requests.UpdateDate) as WorkAge, Status.Description
    from Requests inner join products on Requests.product = products.id
    inner join status on Requests.status = status.id
    where (Requests.Product in (' + @Product + ') or Requests.Product is null) and (Requests.Status in (' + @Status + ') or Requests.Status is null)'

    EXEC(@strSql)

  5. #5
    Join Date
    Jan 2008
    Posts
    23
    ok i found the problem , thnx anyway

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by akkad
    u r laughing ha ,
    No no - I was not laughing at you. George made a minor error and he knows I love nit picking his work.

    Don't use dynamic SQL - that really is a can of worms you do not want to open.

    Code:
    SELECT <define columns, don't use *>
    FROM   orders
    WHERE  orderID = @OrderID
    OR     @OrderID IS NULL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - are you passing a csv of possible values for product and status?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •