Professional Web Applications Themes

Null in SP. - ASP Database

How do I need to adjust this stored procedure to allow it to return all records if 'StateID' is null or empty....does the 'AND' condition contradiction my goal.....should I perhaps pass 'StateBln' as a parameter as well even though '=1' will be the tested condition 99% of the time? CREATE Procedure spr_GetState StateID int As set nocount on select * from State where StateID=StateID AND StateBln=1 return GO Thanks Jason...

  1. #1

    Default Null in SP.

    How do I need to adjust this stored procedure to allow it to return all
    records if 'StateID' is null or empty....does the 'AND' condition
    contradiction my goal.....should I perhaps pass
    'StateBln' as a parameter as well even though '=1' will be the tested
    condition 99% of the time?

    CREATE Procedure spr_GetState
    StateID int
    As
    set nocount on
    select * from State where StateID=StateID AND StateBln=1
    return
    GO

    Thanks
    Jason


    Guest

  2. #2

    Default Re: Null in SP.

    CREATE PROCEDURE dbo.spr_GetState
    StateID INT = NULL
    AS
    BEGIN

    SET NOCOUNT ON

    SELECT <column_list>
    FROM State
    WHERE StateID = COALESCE(StateID, StateID)
    AND StateBln = 1

    END
    GO

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    <com> wrote in message
    news:#phx.gbl... 


    Aaron Guest

  3. #3

    Default Re: Null in SP.

    com wrote: 

    I don't understand your requirement. Do you wish to return a record ONLY if
    StateID is null or empty? Or do you wish to return a record if StateID =
    StateID OR if StateID is null or empty?

    A few rows of sample data and desired results from that sample data will go
    a long way to clarifying your question.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  4. #4

    Default Re: Null in SP.

     

    Sorry, maybe this is more clear:

    If StateID=Null or StateID="" Then Return ALL rows

    If StateID=1 (eg) Then return that SPECIFIC row

    Sample data:
    -------------

    Table State
    ------------

    1. Alabama
    2. Arkansas


    Condition:

    1. IF StateID = Null return: (Alabama, Arkansas)
    2. IF StateID = ""l return: (Alabama, Arkansas)
    3. IF StateID =1 return (Alabama)


    CREATE Procedure spr_GetState
    StateID int
    As
    set nocount on
    select * from State where StateID=StateID AND StateBln=1
    return
    GO



    Guest

  5. #5

    Default Re: Null in SP.

    com wrote: 
    >
    > Sorry, maybe this is more clear:
    >
    > If StateID=Null or StateID="" Then Return ALL rows
    >
    > If StateID=1 (eg) Then return that SPECIFIC row
    >
    > Sample data:
    > -------------
    >
    > Table State
    > ------------
    >
    > 1. Alabama
    > 2. Arkansas
    >
    >
    > Condition:
    >
    > 1. IF StateID = Null return: (Alabama, Arkansas)
    > 2. IF StateID = ""l return: (Alabama, Arkansas)
    > 3. IF StateID =1 return (Alabama)
    >
    >
    > CREATE Procedure spr_GetState
    > StateID int
    > As
    > set nocount on
    > select * from State where StateID=StateID AND StateBln=1
    > return
    > GO[/ref]

    That still makes no sense. Given these rows of sample data:
    StateID Description
    1. Alabama
    "" Empty String
    2. Arkansas
    Null Contains Null

    What do you want the procedure to return when a 1 is passed to it? Just the
    first row? Or the first row and the two rows where StateID is null or ""?

    Or are you saying you want to create three procedures to handle each of the
    three conditions you outlined above?

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  6. #6

    Default Re: Null in SP.

    > That still makes no sense. Given these rows of sample data: 

    I don't think he has rows of data like this. I think his table is:

    1. Alabama
    2. Arkansas

    I think he wants the StateID parameter to be optional, so the user either
    wants a single, specific state or the list of all states. My initial reply
    solved that problem.

    Of course, you can't pass "" to an INT param, and if the StateID is 0 or
    some other non-NULL value that's not in the table, it will return 0 rows.
    So it will only work correctly if the user picks a state from a dropdown
    (populated by the same table) or has the ability to not pick a state (in
    which case the parameter is NULL and all states are returned).

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

  7. #7

    Default Re: Null in SP.

    Aaron [SQL Server MVP] wrote: 
    >
    > I don't think he has rows of data like this. I think his table is:
    >
    > 1. Alabama
    > 2. Arkansas
    >
    > I think he wants the StateID parameter to be optional, so the user
    > either wants a single, specific state or the list of all states. My
    > initial reply solved that problem.
    >[/ref]
    Then why didn't he say

    If StateID=Null or StateID="" Then Return ALL rows

    instead of

    If StateID=Null or StateID="" Then Return ALL rows
    ???

    Rhetorical question. I think you've got it right.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  8. #8

    Default Re: Null in SP.

    If the SP receives a null value or an empty string for incoming parameter
    'StateID' I wish to return all rows:

    StateID State
    1 Alabama
    2. Arkansas

    If the SP recieves a specific StateID ie: StateID=1 then I wish to return
    only that specific row:

    1. Alabama

    This is my calling procedure:

    Set oCmd = GetStoredProcedure(oConn,"spr_GetState2")
    oCmd.Parameters.append oCmd.CreateParameter("StateID", adInteger,
    adParamInput,10,sStateID)
    set oRS = oCmd.execute()

    Response.Write("<TR><TD
    class=text><strong>State:</strong></TD><TD><SELECT NAME=u_StateID>")
    If oRS.eof then
    Response.Write("No records found for state")
    else
    while not oRS.eof
    Response.Write("<OPTION VALUE=""" & oRS.fields(0) & """>" &
    oRS.fields(1) & "</OPTION>")
    oRS.movenext
    wend
    End If

    - Jason


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    > >
    > > Sorry, maybe this is more clear:
    > >
    > > If StateID=Null or StateID="" Then Return ALL rows
    > >
    > > If StateID=1 (eg) Then return that SPECIFIC row
    > >
    > > Sample data:
    > > -------------
    > >
    > > Table State
    > > ------------
    > >
    > > 1. Alabama
    > > 2. Arkansas
    > >
    > >
    > > Condition:
    > >
    > > 1. IF StateID = Null return: (Alabama, Arkansas)
    > > 2. IF StateID = ""l return: (Alabama, Arkansas)
    > > 3. IF StateID =1 return (Alabama)
    > >
    > >
    > > CREATE Procedure spr_GetState
    > > StateID int
    > > As
    > > set nocount on
    > > select * from State where StateID=StateID AND StateBln=1
    > > return
    > > GO[/ref]
    >
    > That still makes no sense. Given these rows of sample data:
    > StateID Description
    > 1. Alabama
    > "" Empty String
    > 2. Arkansas
    > Null Contains Null
    >
    > What do you want the procedure to return when a 1 is passed to it? Just[/ref]
    the 
    the 


    Guest

  9. #9

    Default Re: Null in SP.

    com wrote: 

    The parameter is called "StateID", not "StateID". All this confusion could
    have been avoided ...

    See Aaron's first reply to see how to do this.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  10. #10

    Default Re: Null in SP.

    Jason, you can't pass an empty string to this SP, because it expects an
    INTEGER. An empty string is not an integer and will raise an error. You
    have to pass a valid integer or, if you allow NULLs to be passed in, you can
    pass the keyword NULL.

    Did you read any of my replies?

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    <com> wrote in message
    news:phx.gbl... 
    > >
    > > That still makes no sense. Given these rows of sample data:
    > > StateID Description
    > > 1. Alabama
    > > "" Empty String
    > > 2. Arkansas
    > > Null Contains Null
    > >
    > > What do you want the procedure to return when a 1 is passed to it? Just[/ref]
    > the [/ref]
    ""? 
    > the 
    >
    >[/ref]


    Aaron Guest

  11. #11

    Default Re: Null in SP.

    Apologize for confusion, Aaron is correct...

    The problem as you stated is correct but I am using the SP in two ways:

    1. To return all rows in the dropdown box for a user to select a state
    2. To also pick up the user's drop down box selection after he hits submit.

    This is probably foolish?

    Should I really have two procedures:

    1. To select all the states for the dropdown
    2. To select the individual state chosen by the user

    I am sorry if have ed up this explanation.

    - Jason



    <com> wrote in message
    news:phx.gbl... 
    > >
    > > That still makes no sense. Given these rows of sample data:
    > > StateID Description
    > > 1. Alabama
    > > "" Empty String
    > > 2. Arkansas
    > > Null Contains Null
    > >
    > > What do you want the procedure to return when a 1 is passed to it? Just[/ref]
    > the [/ref]
    ""? 
    > the 
    >
    >[/ref]


    Guest

  12. #12

    Default Re: Null in SP.

    > 2. To select the individual state chosen by the user

    What is the point of this? If they've selected a StateID, store it. Do you
    really need to show the value again? The only reason I can think of is if
    you have a multi-page form, but even then, if you store the stateID, getting
    the state information along with the other demographic information should be
    separate. I think you should have two different stored procedures:

    dbo.spr_getStateList -- gets list of states
    dbo.spr_getUserDemographics -- gets demographics, including name of state
    acquired by join

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

  13. #13

    Default Re: Null in SP.

    Got it...thanks will do as advised and use when referring to parameters.

    - Jason
    "Aaron [SQL Server MVP]" <noraa> wrote in message
    news:phx.gbl... 
    >
    > What is the point of this? If they've selected a StateID, store it. Do[/ref]
    you 
    getting 
    be 


    Guest

Similar Threads

  1. "null null" error on line -1
    By multiscan01 in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: August 10th, 06:20 PM
  2. NULL NULL Error
    By Packer_Fan in forum Macromedia ColdFusion
    Replies: 2
    Last Post: August 10th, 05:14 PM
  3. Error: ?null? is null or not an object
    By Murray *TMM* in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 10th, 06:36 PM
  4. How to ensure if column A is null, column B has to be null
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 07:09 PM
  5. Null > ''
    By Jason Davis in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 6th, 07:47 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