passing several parameters to a stored procedure

[Dan]

I'm using Sql Server 6.5. I want to create a search form that will have four
fields on it. Customer name, city, state and zip code. I want the user to
be able to enter no search criteria, in which case, all rows from the table
would be returned or the user could enter information in one, two, three
or all four fields and in any combination.

I've seen where a default value can be assigned in the stored procedure.
Is it possible to default the value to "all" using the "%"? If the user only
fills in one field on the search form, how do I let the stored procedure
know that this field represents the third variable in the stored procedure?
Are there placeholders that I can use to pass in with the one field that
the user has filled in so the stored procedure knows to match the value passed
in with the third variable in the stored procedure.

Thanks,

Dan

[C. E. Buttles]

Use CASE to evaluate the content of the variable, then process based on
that.

CASE @var='x'
do something
CASE @var-'y'
do something else
CASE @var='z'
do yet another thing


"Dan" <ddonahue@midsouth.rr.com> wrote in message
news:38dae0b7$1@news.devx.com...
>
> I'm using Sql Server 6.5. I want to create a search form that will have
four
> fields on it. Customer name, city, state and zip code. I want the user to
> be able to enter no search criteria, in which case, all rows from the
table
> would be returned or the user could enter information in one, two, three
> or all four fields and in any combination.
>
> I've seen where a default value can be assigned in the stored procedure.
> Is it possible to default the value to "all" using the "%"? If the user
only
> fills in one field on the search form, how do I let the stored procedure
> know that this field represents the third variable in the stored
procedure?
> Are there placeholders that I can use to pass in with the one field that
> the user has filled in so the stored procedure knows to match the value
passed
> in with the third variable in the stored procedure.
>
> Thanks,
>
> Dan

[Dan]

I am new to SQL Server and stored procedures so I'm not sure that I understand
what you're suggesting. Are you saying that I would use four arguments when
executing the stored procedure(i.e. search_procedure str_name, str_city,
str_state, str_zip) but that some or all of them could be null? And then,
inside the stored procedure, use a case statement to evaluate whether each
variable is null or not. But if the variable is null, how do I tell the stored
procedure that I want to get "all"(%) for that variable?

Thanks,

Dan

"C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
>Use CASE to evaluate the content of the variable, then process based on
>that.
>
>CASE @var='x'
> do something
>CASE @var-'y'
> do something else
>CASE @var='z'
> do yet another thing
>
>

[C. E. Buttles]

Something like this might be easier:

CREATE PROCEDURE sp_listlook @str_col varchar(20)=NULL, @str_lookfor
varchar(50)=NULL

You could pass two arguments: what column (name, city, etc.) and what you
are looking for in that column (%, D%, John, etc.). Then:

DECLARE @sqlstr varchar(255)
DECLARE @endres varchar(255) #Use this variable for output. See BOL.

BEGIN
SELECT @sqlstr="""+'SELECT '+@str_lookfor+'FROM '+@str_col"""
EXEC @sqlstr
END


I didn't put the output variable in and the quotes may be messed up. It is
Friday and quitting time, but this should give you a start. Be sure to read
up in BOL.


"Dan" <ddonahue@midsouth.rr.com> wrote in message
news:38dbd4ac$1@news.devx.com...
>
> I am new to SQL Server and stored procedures so I'm not sure that I
understand
> what you're suggesting. Are you saying that I would use four arguments
when
> executing the stored procedure(i.e. search_procedure str_name, str_city,
> str_state, str_zip) but that some or all of them could be null? And then,
> inside the stored procedure, use a case statement to evaluate whether each
> variable is null or not. But if the variable is null, how do I tell the
stored
> procedure that I want to get "all"(%) for that variable?
>
> Thanks,
>
> Dan
>
> "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
> >Use CASE to evaluate the content of the variable, then process based on
> >that.
> >
> >CASE @var='x'
> > do something
> >CASE @var-'y'
> > do something else
> >CASE @var='z'
> > do yet another thing
> >
> >
>

[Dan]

I think that will work. We actually need to put the string in the "where"
clause. I'll give it a try.

Thanks.

Dan

"C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
>Something like this might be easier:
>
>CREATE PROCEDURE sp_listlook @str_col varchar(20)=NULL, @str_lookfor
>varchar(50)=NULL
>
>You could pass two arguments: what column (name, city, etc.) and what you
>are looking for in that column (%, D%, John, etc.). Then:
>
>DECLARE @sqlstr varchar(255)
>DECLARE @endres varchar(255) #Use this variable for output. See BOL.
>
>BEGIN
>SELECT @sqlstr="""+'SELECT '+@str_lookfor+'FROM '+@str_col"""
>EXEC @sqlstr
>END
>
>