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
-
i think you might need some more information here. what are you planning on doing with these ids? are they to go into a where clause?nathan gonzalez– nathan gonzalez2011-04-07 16:19:40 +00:00Commented Apr 7, 2011 at 16:19
-
Yes nathan, its going to where clause.to update perticular Ids in the tables. using this sP.user300485– user3004852011-04-07 16:21:08 +00:00Commented Apr 7, 2011 at 16:21
Add a comment
|
4 Answers
In SQL Server 2008 and up you can use Table-Valued Parameters
3 Comments
nathan gonzalez
also helpful, here is a link showing how to call it from .net dotnetspeaks.com/DisplayArticle.aspx?ID=47
Cade Roux
@nathan gonzalez Sorry, I thought that link had an example - I've been doing a bit of those TVPs lately myself. This link has a better complete overview: msdn.microsoft.com/en-us/library/bb675163.aspx
Solomon Rutzky
that msdn link does have a good range of examples, but the last one regarding the streaming option is a bit terse. I wrote an article specifically detailing the streaming option here: sqlservercentral.com/articles/SQL+Server+2008/66554 (registration is required).
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
1 Comment
Lucero
For 2005 (and even 2000), XML is usually the better way then CSV. Cleaner and faster, and easily extensible to multiple columns. From the article you link to: "In fact, XML is the fastest method that does not require any preparations in the server: you don't have to activate the CLR and create a function, nor do you need to define a table type for a table-valued parameter."
What about a comma delimited string of Id's?
The problem is sql server doesn't support an array data type (or similar)
2 Comments
user300485
yes we can send string of id's but how to seperate those string of ids' with comma delimited in the sp.
Darkwing
Looks like Rene147 has added a useful link
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.
