How can I send array to stored procedure? I want send it from JS to stored procedure. And what is the best data format for this task?
-
ok afaik you can use HTML5's Web Database API docs to allow the client to interact with a database on the client side. To allow the client to interact with a remote database you would have to implement a server side language such as Java / PHP to act as an intermediary between client and database.T I– T I2012-02-01 14:48:42 +00:00Commented Feb 1, 2012 at 14:48
Add a comment
|
4 Answers
SQL 2008 supports table valued parameters - so you can pass a table of data as a parameter to a sproc. The common ways prior to this, were as mentioned already - via a CSV or XML parameter.
4 Comments
Anton Lyhin
People say that there can be an issue with count of symbols: "There can not be more then 8000 symbols in such string". It is right? Is it a good idea to use CSV instead of XML because of it (it's not supposed to use this string anywhere alse).
AdaTheDev
@Anton Not quite sure what you are referring to - 8000 is the largest size you can define for a VARCHAR before you have to VARCHAR(MAX) which supports up to 2GB worth of text. XML datatype supports up to 2GB too. Problem with these is you then have to parse/shred the values out. Hence I'd recommend looking at the table valued parameter approach. I compared the performance of all 3 on my blog here: adathedev.co.uk/2010/02/…
Anton Lyhin
One more question. What is better way to do the work: to call sproc with array param or sproc many times one by one in one transaction? Thank you.
AdaTheDev
I would pass in a batch and do it in fewer calls, not one at a time.
With MS SQL you should be able to consume it as comma-delimited.
answered Feb 1, 2012 at 14:48
Diodeus - James MacFarlane
115k33 gold badges164 silver badges180 bronze badges
Comments
I've used XML to pass arrays of data into SQL server in the past, and it works quite well with XML data types and XQuery.
Comments
Here's a simple example using a CSV input parameter to the stored proc.
create proc dbo.ListCustomers
(
@CustomerIDs varchar(MAX)
)
as
begin
-- convert paramter to xml
declare @XmlStr varchar(MAX)
set @XmlStr = '<R><I ID="' + replace(@CustomerIDs, ',', '"></I><I ID="') + '"></I></R>'
-- table variable that holds the ID's
declare @IdTable table (ID int)
-- load the XML document and insert id's to @IdTable
declare @XmlDoc int
exec sp_xml_preparedocument @XmlDoc out, @XmlStr
insert into @IdTable select ID from openxml(@XmlDoc, '/R/I',1) with (ID int)
exec sp_xml_removedocument @XmlDoc
-- use @IdTable in your query
select c.*
from tblCustomer c
join @IdTable as I on c.CustomerID = I.ID
end
go
-- usage:
-- exec ListCustomers '3823,3838,3845,3925,4051'
