Professional Web Applications Themes

Passing null for select - Microsoft SQL / MS SQL Server

We're doing a stored procedure for a search page with a number of optional parameters. If they're not entered by the user, a Null is passed to SQL Server for that parameter. It all works fine, except for one parameter: When the parmResultDate (which is actually a smallint, not a date) is passed as a null in Query yser, it returns nothing for the search. If we set it to 0, 7, 14, or 30, it works fine. How do we get it to recognize Nulls? Thanks. Here's the section of the code that's not working right: .. . . ...

  1. #1

    Default Passing null for select

    We're doing a stored procedure for a search page with a number of optional
    parameters. If they're not entered by the user, a Null is passed to SQL
    Server for that parameter. It all works fine, except for one parameter: When
    the parmResultDate (which is actually a smallint, not a date) is passed as
    a null in Query yser, it returns nothing for the search. If we set it to
    0, 7, 14, or 30, it works fine. How do we get it to recognize Nulls? Thanks.
    Here's the section of the code that's not working right:

    .. . . .
    AND (
    (parmResultDate Is Null and dateApproved =
    Coalesce(parmResultDate,dateApproved))
    OR (parmResultDate = 0 and (DateDiff(Day,dateApproved,getDate()) =
    0))
    OR (parmResultDate = 7 and (DateDiff(Day,dateApproved,getDate()) <=
    7))
    OR (parmResultDate = 14 and (DateDiff(Day,dateApproved,getDate()) <=
    14))
    OR (parmResultDate = 30 and (DateDiff(Day,dateApproved,getDate()) <=
    30))
    )
    .. . . .


    dw Guest

  2. #2

    Default Re: Passing null for select

    How about


    AND ((
    (dateApproved = COALESCE(parmResultDate, dateApproved))
    OR
    (parmResultDate IN (0, 7, 14, 30)
    AND DATEDIFF(DAY, dateApproved, GETDATE()) <= parmResultDate)
    )


    "dw" <cougarmana_NOSPAMuncw.edu> wrote in message
    news:#blPNBVRDHA.2144TK2MSFTNGP11.phx.gbl...
    > We're doing a stored procedure for a search page with a number of optional
    > parameters. If they're not entered by the user, a Null is passed to SQL
    > Server for that parameter. It all works fine, except for one parameter:
    When
    > the parmResultDate (which is actually a smallint, not a date) is passed
    as
    > a null in Query yser, it returns nothing for the search. If we set it
    to
    > 0, 7, 14, or 30, it works fine. How do we get it to recognize Nulls?
    Thanks.
    > Here's the section of the code that's not working right:
    >
    > . . . .
    > AND (
    > (parmResultDate Is Null and dateApproved =
    > Coalesce(parmResultDate,dateApproved))
    > OR (parmResultDate = 0 and (DateDiff(Day,dateApproved,getDate()) =
    > 0))
    > OR (parmResultDate = 7 and (DateDiff(Day,dateApproved,getDate()) <=
    > 7))
    > OR (parmResultDate = 14 and (DateDiff(Day,dateApproved,getDate())
    <=
    > 14))
    > OR (parmResultDate = 30 and (DateDiff(Day,dateApproved,getDate())
    <=
    > 30))
    > )
    > . . . .
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: Passing null for select

    Thanks, Aaron. That's such clean code! I'll plug it in right now. : )
    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:eaTXJPVRDHA.2408TK2MSFTNGP10.phx.gbl...
    > How about
    >
    >
    > AND ((
    > (dateApproved = COALESCE(parmResultDate, dateApproved))
    > OR
    > (parmResultDate IN (0, 7, 14, 30)
    > AND DATEDIFF(DAY, dateApproved, GETDATE()) <= parmResultDate)
    > )
    >
    >
    > "dw" <cougarmana_NOSPAMuncw.edu> wrote in message
    > news:#blPNBVRDHA.2144TK2MSFTNGP11.phx.gbl...
    > > We're doing a stored procedure for a search page with a number of
    optional
    > > parameters. If they're not entered by the user, a Null is passed to SQL
    > > Server for that parameter. It all works fine, except for one parameter:
    > When
    > > the parmResultDate (which is actually a smallint, not a date) is passed
    > as
    > > a null in Query yser, it returns nothing for the search. If we set
    it
    > to
    > > 0, 7, 14, or 30, it works fine. How do we get it to recognize Nulls?
    > Thanks.
    > > Here's the section of the code that's not working right:
    > >
    > > . . . .
    > > AND (
    > > (parmResultDate Is Null and dateApproved =
    > > Coalesce(parmResultDate,dateApproved))
    > > OR (parmResultDate = 0 and (DateDiff(Day,dateApproved,getDate())
    =
    > > 0))
    > > OR (parmResultDate = 7 and (DateDiff(Day,dateApproved,getDate())
    <=
    > > 7))
    > > OR (parmResultDate = 14 and (DateDiff(Day,dateApproved,getDate())
    > <=
    > > 14))
    > > OR (parmResultDate = 30 and (DateDiff(Day,dateApproved,getDate())
    > <=
    > > 30))
    > > )
    > > . . . .
    > >
    > >
    >
    >

    dw Guest

Similar Threads

  1. Passing null into a stored procedure
    By carmenreed23 in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 13th, 03:37 PM
  2. passing variables from 'select' element in a form
    By Thunderbirds in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: February 23rd, 09:05 AM
  3. passing variables from select elements in form
    By Thunderbirds in forum Macromedia ColdFusion
    Replies: 0
    Last Post: February 18th, 03:15 PM
  4. Passing a Null to Web Method
    By J. L. Goddard in forum ASP.NET Web Services
    Replies: 7
    Last Post: August 3rd, 08:00 PM
  5. Select a row from a DataGrid1 by passing a QueryString variable
    By David Dimmer in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: April 20th, 08:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139