CODEWITHSUNDEEP
javascriptsql-server-2008stored-procedures
Anton Lyhin
Anton Lyhin

Reputation: 1945

How can I send array to stored procedure? What is the best data format if I send it from JS?

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?

Upvotes: 0

Views: 204

Answers (4)

jim31415
jim31415

Reputation: 8808

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'

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147304

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.

MSDN Reference

Upvotes: 1

ShaneBlake
ShaneBlake

Reputation: 11096

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.

Upvotes: 1

Diodeus - James MacFarlane
Diodeus - James MacFarlane

Reputation: 114417

With MS SQL you should be able to consume it as comma-delimited.

Upvotes: 1

Related Questions