DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: passing several parameters to a stored procedure

  1. #1
    Dan Guest

    passing several parameters to a stored procedure


    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

  2. #2
    C. E. Buttles Guest

    Re: passing several parameters to a stored procedure

    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



  3. #3
    Dan Guest

    Re: passing several parameters to a stored procedure


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



  4. #4
    C. E. Buttles Guest

    Re: passing several parameters to a stored procedure

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

    >



  5. #5
    Dan Guest

    Re: passing several parameters to a stored procedure


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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center