Stored Procedure.. using sql server

Can any tell me how to send the LIst of IDs to the stored procedure in sql.

I need to send the List from My Controller so that that List of ID's can execute at a time开发者_运维知识库 using stored procedure.

Thanks

---

In SQL Server 2008 and up you can use Table-Valued Parameters

---

The best way (in 2008) is to pass it as a table. Pre 2008 you had to use a CSV format VarChar then split it out.

Have a read of this: http://www.sommarskog.se/arrays-in-sql-2008.html

---

What about a comma delimited string of Id's?

The problem is sql server doesn't support an array data type (or similar)

---

Sounds like you need something along the lines of this:

CREATE FUNCTION [dbo].[Split_String]
(
    @ConcatValues VARCHAR(MAX)
)  
RETURNS @Values Table 
(
     Value VARCHAR(MAX)
)
AS  
/**************************************************************************************************************
Purpose:    When called from a stored procedure and passed a character delimited parameter (of String data type values), 
            this function returns a table named "@Values" with a field named "Value" (populated with the parameter list)
            which can then be referenced in the stored procedure.
            This function requires that the delimited paramater have as its first character the delimiter character.

Sample calls: 
            Select * from [dbo].[Split_String](';dog;cat;mouse')
            Select * from [dbo].[Split_String]('| dog| cat| mouse|')        
            Select * from [dbo].[Split_String]('|')     
            Select * from [dbo].[Split_String]('')      

**************************************************************************************************************/

BEGIN 
    --Indexes to keep the position of searching
    DECLARE @Delim CHAR(1)
    Declare @Pos1 Int
    Declare @Pos2 Int

    --Identify delimiter character
    SET @Delim = SUBSTRING(@ConcatValues, 1, 1)
    --Append delimiter character
    Set @ConcatValues = @ConcatValues + ' ' + @Delim    
    --Set 1st character of 1st value
    Set @Pos2 = 2

    While @Pos2 < Len(@ConcatValues)
    BEGIN
        Set @Pos1 = CharIndex(@Delim, @ConcatValues, @Pos2)
        Insert @Values SELECT LTRIM(RTRIM(Substring(@ConcatValues, @Pos2, @Pos1 - @Pos2)))
        --Go to next non-delimiter character
        Set @Pos2 = @Pos1 + 1
    END
    RETURN
END


GO

Our split function is generic for use in a wide variety of situations and is dependent on the delimiter being identified by the first character in the string. It is likely that it could be simplified a bit if you only need it in one spot.