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

[Aaron]

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... 

[Bob]

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.

 

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

[Bob]

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.

[Aaron]

> 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.)

[Bob]

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.

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 

[Bob]

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.

[Aaron]

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]

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 screwed 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]

[Aaron]

> 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.)

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