Passing Array to Store Procedure in SQL

Passing the array as SQL Parameter is always a concern becuase running the ExceuteQuery for multiple Insert is not a good option through .NET gives you the Implementation of Transaction but this will only serve concurrency issue but somewhere er have to face the performance issue because of this.

So, the need to pass the array to Store Procedure is very comman and the best approch to do this is pass the xml to the Store Procedure in ntext variable.


Create procedure [dbo].[spSendXML]
(
@RecordXML ntext
)
AS

Begin

create Table #tbl (ID int)

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @RecordXML

insert into #tbl
select ID
FROM OPENXML(@idoc,'/Root/Comp', 1)
WITH (ID varchar(50))


End


By doing this you can get all the data in SQL Temp Table.


Calling of the same Store Procedure.

spSendXML ''

Thanks
Dalip Vohra