Sending Variable Values to Parameter In Stored Procedures.

Discussion in 'ASP .Net Mobile' started by A. Shiraz, Jul 4, 2004.

  1. A. Shiraz

    A. Shiraz Guest

    I have scanned the various books on SQL Server and ASP .net but I cannot find anything on accomplishing the following :

    - how to send a list of variable values to an SQL stored procedure using visual C# (studio 2003) and SQL Server 2000 Dev. as backend. Allow me to illustrate :

    Suppose I have an sql statement that says :

    Select * from Activities where zip_code IN ("10023","10024"...."10029");

    Now if I have a stored procedure like so :

    Create Procedure activity_by_zip @zip varchar(1000)
    As Select * from Activities where zip_code IN (@zip);

    Now I would like @zip parameter to be a list of zip codes.

    I get the zip codes in a dataset and I would like to use those zips to search over the records as above.

    I searched the MSDN library and found allusions to SqlParameters[] but there is not much usage information or examples. Am I on the right track?

    I cannot find any of these details in the ASP.net or Ado.net books out there. It is simply this : what if I wanted to send a stored procedure an array as a parameter such that the stored procedure could execute IN operations on multiple values as so:

    Select * from Activities where zip_code IN ("10023","10024"...."10029");

    Thank you for your help and for reading.
     
    A. Shiraz, Jul 4, 2004
    #1
    1. Advertising

  2. I have done this before but not by using an array but by converting your multiple values into a comma delimited string, for example:

    create procedure myProc(@vals VARCHAR(20))
    as
    SELECT *
    FROM myTable
    WHERE myCol IN ( @vals )

    As long as you ensure that your values are comma delimited (each seperated by a comma) this should work fine.



    "A. Shiraz" wrote:

    > I have scanned the various books on SQL Server and ASP .net but I cannot find anything on accomplishing the following :
    >
    > - how to send a list of variable values to an SQL stored procedure using visual C# (studio 2003) and SQL Server 2000 Dev. as backend. Allow me to illustrate :
    >
    > Suppose I have an sql statement that says :
    >
    > Select * from Activities where zip_code IN ("10023","10024"...."10029");
    >
    > Now if I have a stored procedure like so :
    >
    > Create Procedure activity_by_zip @zip varchar(1000)
    > As Select * from Activities where zip_code IN (@zip);
    >
    > Now I would like @zip parameter to be a list of zip codes.
    >
    > I get the zip codes in a dataset and I would like to use those zips to search over the records as above.
    >
    > I searched the MSDN library and found allusions to SqlParameters[] but there is not much usage information or examples. Am I on the right track?
    >
    > I cannot find any of these details in the ASP.net or Ado.net books out there. It is simply this : what if I wanted to send a stored procedure an array as a parameter such that the stored procedure could execute IN operations on multiple values as so:
    >
    > Select * from Activities where zip_code IN ("10023","10024"...."10029");
    >
    > Thank you for your help and for reading.
     
    Clint Colefax, Jul 6, 2004
    #2
    1. Advertising

  3. No, this won't work. It's been tried many times. There are a number of
    whitepapers out there that describe this problem and ways to get around it.
    One innovative approach is to pass in a delimited string and use a SQL
    function to parse the string and return a Table type object to use in the IN
    expression.

    hth

    --
    ____________________________________
    William (Bill) Vaughn
    Author, Mentor, Consultant
    Microsoft MVP
    www.betav.com
    Please reply only to the newsgroup so that others can benefit.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    __________________________________

    "Clint Colefax" <> wrote in message
    news:...
    > I have done this before but not by using an array but by converting your

    multiple values into a comma delimited string, for example:
    >
    > create procedure myProc(@vals VARCHAR(20))
    > as
    > SELECT *
    > FROM myTable
    > WHERE myCol IN ( @vals )
    >
    > As long as you ensure that your values are comma delimited (each seperated

    by a comma) this should work fine.
    >
    >
    >
    > "A. Shiraz" wrote:
    >
    > > I have scanned the various books on SQL Server and ASP .net but I cannot

    find anything on accomplishing the following :
    > >
    > > - how to send a list of variable values to an SQL stored procedure using

    visual C# (studio 2003) and SQL Server 2000 Dev. as backend. Allow me to
    illustrate :
    > >
    > > Suppose I have an sql statement that says :
    > >
    > > Select * from Activities where zip_code IN ("10023","10024"...."10029");
    > >
    > > Now if I have a stored procedure like so :
    > >
    > > Create Procedure activity_by_zip @zip varchar(1000)
    > > As Select * from Activities where zip_code IN (@zip);
    > >
    > > Now I would like @zip parameter to be a list of zip codes.
    > >
    > > I get the zip codes in a dataset and I would like to use those zips to

    search over the records as above.
    > >
    > > I searched the MSDN library and found allusions to SqlParameters[] but

    there is not much usage information or examples. Am I on the right track?
    > >
    > > I cannot find any of these details in the ASP.net or Ado.net books out

    there. It is simply this : what if I wanted to send a stored procedure an
    array as a parameter such that the stored procedure could execute IN
    operations on multiple values as so:
    > >
    > > Select * from Activities where zip_code IN ("10023","10024"...."10029");
    > >
    > > Thank you for your help and for reading.
     
    William \(Bill\) Vaughn, Jul 6, 2004
    #3
  4. A. Shiraz

    Stu Guest

    "William \(Bill\) Vaughn" <> wrote in message news:<>...
    <snip>
    > One innovative approach is to pass in a delimited string and use a SQL
    > function to parse the string and return a Table type object to use in the IN
    > expression.


    Yeah, I use this method and it works nicely. Something along the lines of:

    CREATE TABLE #tmp (item)

    /*

    parse string parameter of form "a b c d e" - based on delimiter

    (see handy routines: charindex, cast, ltrim, etc)

    insert each item into #tmp

    */

    -- now grab the data
    SELECT *
    FROM myTable
    WHERE myField IN (SELECT item FROM #tmp)
     
    Stu, Jul 7, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Taras
    Replies:
    2
    Views:
    4,822
    Rick Spiewak
    Oct 5, 2003
  2. Soumitra Banerjee

    Putting stored procedures in a dll

    Soumitra Banerjee, Feb 27, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    411
    Soumitra Banerjee
    Feb 27, 2004
  3. Rob Wire
    Replies:
    5
    Views:
    499
    Yan-Hong Huang[MSFT]
    Aug 14, 2003
  4. scparker
    Replies:
    4
    Views:
    398
    scparker
    Nov 2, 2006
  5. jcpc
    Replies:
    2
    Views:
    1,509
Loading...

Share This Page