how to pass dataset to stored procedure as input parameter? [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Pass a list-structure a开发者_运维百科s an argument to a stored procedure

I am having a session variable that contains multiple comma separated values.I stored these values in an array using Split function.Now I need to pass this array to the stored procedure.Is this possible and if yes how can I use this array in stored procedure?

---

If you are using Sql Server 2008, try passing it as a table value parameter:

Table Value Parameter in SQL Server 2008 and .NET (C#)

---

I've ran into this exact same scenario before and here is what I did:

Instead of splitting the variable into an array in code I simply passed the entire string of comma delimited text to a stored procedure as an nvarchar. Then I created a database function that takes the value passed in as well as a delimiter and returns a table of the splitted values. See below...

    CREATE FUNCTION [dbo].[split]
    (
        @DataToSplit NVARCHAR(MAX),
        @Delimiter NVARCHAR(5)
    )
    RETURNS @ReturnVal AS TABLE
    (
        ID INT IDENTITY(1, 1),
        Item NVARCHAR(200)
    )
    AS
    BEGIN

        WHILE (CHARINDEX(@DELIMITER, @DataToSplit) > 0)
        BEGIN

            INSERT INTO @ReturnVal
            (
                Item
            )
            VALUES
            (
                SUBSTRING(@DataToSplit, 1, CHARINDEX(@Delimiter, @DataToSplit) - 1)
            )

            SET @DataToSplit = SUBSTRING(@DataToSplit, CHARINDEX(@Delimiter, @DataToSplit) + 1, LEN(@DataToSplit))
        END

        -- Grab the last item of the separated list of items
        INSERT INTO @ReturnVal
        (
            Item
        )
        VALUES
        (
            @DataToSplit
        )

        RETURN

    END

Once you have your split function created you can use it in the stored proc that you are passing the delimited text into and use it in various ways such as...

    CREATE PROCEDURE [dbo].[ExampleProc]
        @Values NVARCHAR(MAX)
    AS

        SET NOCOUNT ON;

        SELECT Item
        FROM [dbo].[split](@Values, ',')

        -- OR

        SELECT Item
        FROM [dbo].[SomeTable]
        WHERE Item IN 
        (
           SELECT Item FROM [dbo].[split](@Values, ',')
        )

        -- OR

        SELECT a.Item
        FROM [dbo].[SomeTable] AS a
        INNER JOIN [dbo].[split](@Values, ',') AS b
            ON a.ID = b.ID
    GO