Best way to pass long list of ID's to stored procedure?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Best way to pass long list of ID's to stored procedure?

Hybrid View

  1. #1
    Matthew Solnit Guest

    Best way to pass long list of ID's to stored procedure?

    If I need to pass to pass a long list of ID's to a stored procedure, what is the
    best way to do it? Right now, I have a parameter defined as varchar(8000),
    which is a delimited list. I then have to loop through this string and operate
    on each extracted ID, or I can use EXEC() and the IN clause to create a
    temporary table, which I can then use with a JOIN or to create a cursor, sort of
    like this:

    CREATE PROCEDURE test
    @id_list varchar(8000) /* comma-delimited list of id's */
    AS
    SET @strSql = 'SELECT * INTO #tmp FROM table1 WHERE table1.id IN (' + @id_list +
    ')'
    EXEC (@strSql)
    /* now do whatever with #tmp */
    DROP TABLE #tmp


    Is there a more elegant way? Please note that I am using SQL Server 7.0.

    -- Matthew Solnit


    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: Best way to pass long list of ID's to stored procedure?

    It really depends on the source of the ID list. If the ID list is from
    another query, then I would either encapsulate that query into the sproc (if
    the query is static or can be parameterized) or select the results of that
    query into a temp table prior to calling the sproc. Note that the later
    option means that your sproc is going to expect the temp table to exist
    already - if it doesn't, you're going to get a runtime error.

    Yet another option is to create a permanent working table. You would create
    some form of process id (PID) to server as part of the key to that table.
    You then pass the PID into the sproc - the sproc cleans up the working table
    when it has completed its work. The problem with this solution is that the
    working table will likely become cluttered with dead records over time from
    instances where the sproc failed for some reason (system failure for
    example).

    HTH,
    --
    Rob Vieira MCSD, MCT, MCDBA
    www.ProfessionalSQL.com

    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote in message
    news:3a6c971c@news.devx.com...
    > If I need to pass to pass a long list of ID's to a stored procedure, what

    is the
    > best way to do it? Right now, I have a parameter defined as

    varchar(8000),
    > which is a delimited list. I then have to loop through this string and

    operate
    > on each extracted ID, or I can use EXEC() and the IN clause to create a
    > temporary table, which I can then use with a JOIN or to create a cursor,

    sort of
    > like this:
    >
    > CREATE PROCEDURE test
    > @id_list varchar(8000) /* comma-delimited list of id's */
    > AS
    > SET @strSql = 'SELECT * INTO #tmp FROM table1 WHERE table1.id IN (' +

    @id_list +
    > ')'
    > EXEC (@strSql)
    > /* now do whatever with #tmp */
    > DROP TABLE #tmp
    >
    >
    > Is there a more elegant way? Please note that I am using SQL Server 7.0.
    >
    > -- Matthew Solnit
    >
    >



    Share on Google+

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