Passing multiple values to a Stored Procedure

  • Currently I have a standard query that passes multiple (dynamic) values(1,2,3,5,6,7,8,9,0,11,12) from a checkbox selection(middle tier- .net) to the database.  The query then runs on the values added:

    Something like this:

    SELECT name FROM table WHERE id IN(1,2,3,5,6,7,8,9,0,11,12)

    I would like to convert this into a stored procedure...How would I pass these values through (the number of variables may be between 0 and 1000 - for the sake of this example)?

    Can anybody help me with this problem, and possibly the syntax?

    Thanks

    Kevin

     

  • Can you pass this list as a comma-delimted string e.g. "1,2,3,4,5,6,7"?

    If so, you could use

    CREATE PROC dbo.spProc

    @inputstring varchar(8000)

    AS

    EXEC('SELECT name FROM table WHERE id IN('+@inputstring+')')

    GO

    If not, the only other way I could think of, albeit slightly messy, is to write a procedure with 1000 optional parameters, i.e. set each one with a default equal to some input you couldn't get back from the app, and then create a string from these.  If you named the variables with a number at the end e.g. @var1, @var2 etc. then you could easily run this in a loop.

    HTH

  • http://www.sommarskog.se/arrays-in-sql.html

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Was doing that today myself for a project.

    We pass in with a varchar or multiple varchars if it is going to be longer than one can handle.

    My example only allows for one thou.

    CREATE PROC ip_MyProc

     @vals varchar(8000)

    AS

    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..#tblVals') IS NOT NULL

     DROP TABLE #tblVals

    CREATE TABLE #tblVals (

     val int

    )

    DECLARE @pos int

    SET @pos = charindex(',',@vals)

    WHILE @pos > 0

    BEGIN

     INSERT #tblVals (val) VALUES (cast(left(@vals, @pos - 1) as int))

     SET @vals = right(@vals, LEN(@vals) - @pos)

     SET @pos = charindex(',',@vals)

    END

    IF LEN(@vals) > 0

     INSERT #tblVals (val) VALUES (cast(@vals AS INT))

    SELECT * FROM

     tblName N

    INNER JOIN

     #tblVals V

    ON

     N.columnname = V.val

    DROP TABLE #tblVals

     

    This works in both 2000 and 7

    However if using 2000 you might do this instead.

    CREATE FUNCTION dbo.fn_ParsedTable (@vals varchar(8000))

     RETURNS @tblVals TABLE (

      val int

    )

    AS

    BEGIN 

     DECLARE @pos int

     

     SET @pos = charindex(',',@vals)

     

     WHILE @pos > 0

     BEGIN

      INSERT @tblVals (val) VALUES (cast(left(@vals, @pos - 1) as int))

      SET @vals = right(@vals, LEN(@vals) - @pos)

      SET @pos = charindex(',',@vals)

     END

     

     IF LEN(@vals) > 0

      INSERT @tblVals (val) VALUES (cast(@vals AS INT))

     RETURN

    END

    Then you can use like so

    SELECT * FROM

     tblName N

    INNER JOIN

     dbo.fn_ParsedTable('1,2,3') V

    ON

     N.columnname = V.val

    but the great thing is it can be reused for other items.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply