Sql Server - pass array to procedure

Question

I need to pass set of int to sql procedure.

alter PROC PrCustomerServicesUpd(
                                     @CustomerId UNIQUEIDENTIFIER,
                                     @ServicesIdXml NVARCHAR(1000),
                                     @ServicesIdCount INT =1
)
AS 
DECLARE @XmlDocHanle INT
EXEC sp_Xml_PrepareDocument @XmlDocHanle OUTPUT, @ServicesIdXml

SELECT * FROM OPENXML(@XmlDocHanle, '/ROOT/Services/ServicesId',@ServicesIdCount)
WITH (ServiceId INT)


EXEC sp_Xml_RemoveDocument @XmlDocHanle 



go
PrCustomerServicesUpd '443c293e-fc78-4562-97f8-ee1f2b54f813'
,'<Services><ServiceId>12</ServiceId><ServiceId>156</ServiceId></Services>',22

This script returns one empty field named 'ServiceId' instead of 2 rows.

RichardTheKiwi · Accepted Answer · 2011-04-07 07:35:52Z

3

Assuming this is SQL Server 2000 (otherwise there is no sane reason to use OPENXML), you need to get the text() within the node, otherwise it tries to find the attribute "ServiceId" within the node "ServiceId". Also your parameter XML is completely out of sync with what the proc expects.

alter PROC PrCustomerServicesUpd(
                                     @CustomerId UNIQUEIDENTIFIER,
                                     @ServicesIdXml NVARCHAR(1000),
                                     @ServicesIdCount INT =1
)
AS 
DECLARE @XmlDocHanle INT
EXEC sp_Xml_PrepareDocument @XmlDocHanle OUTPUT, @ServicesIdXml

SELECT * FROM OPENXML(@XmlDocHanle, '/ROOT/Services/ServicesId',@ServicesIdCount)
WITH (ServiceId INT 'text()')


EXEC sp_Xml_RemoveDocument @XmlDocHanle 
GO

PrCustomerServicesUpd '443c293e-fc78-4562-97f8-ee1f2b54f813'
,'<ROOT><Services><ServicesId>12</ServicesId><ServicesId>156</ServicesId></Services></ROOT>',3

Using the XML data type

alter PROC PrCustomerServicesUpd
 @CustomerId UNIQUEIDENTIFIER,
 @ServicesIdXml xml,
 @ServicesIdCount INT =1
AS
select service.id.value('.','int')
from @ServicesIdXml.nodes('/ROOT/Services/ServicesId') service(id)
GO

edited Apr 7, 2011 at 7:35

answered Apr 7, 2011 at 7:30

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alexandre Over a year ago

It works properly. What do you recommend to use for sql server 2008 r2 instead of openxml ?

RichardTheKiwi Over a year ago

@Alex / Please see xml data type usage in the answer

Alexandre Over a year ago

Do you mean 'text()'? What does it mean?

RichardTheKiwi Over a year ago

@alex / No I mean the 2nd code block. That's the proper, better way to do it in 2008 R2.

Alexandre Over a year ago

Good! I have not known about it!

harshagile · Accepted Answer · 2011-04-07 07:23:18Z

You can pass set of int in varchar(1000) with comma separator

so int value passing as "1223,12,254,5545,8787,8787,787,78,45475,45,45"

And in store procedure you can get one by one id with fnSplit function (Tabular).

ALTER function [dbo].[fnSplit](
 @String nvarchar (4000),
 @Delimiter nvarchar (10)
 )
returns @ValueTable table ([Value] nvarchar(4000))
begin
 declare @NextString nvarchar(4000)
 declare @Pos int
 declare @NextPos int
 declare @CommaCheck nvarchar(1)

 --Initialize
 set @NextString = ''
 set @CommaCheck = right(@String,1) 

 --Check for trailing Comma, if not exists, INSERT
 --if (@CommaCheck <> @Delimiter )
 set @String = @String + @Delimiter

 --Get position of first Comma
 set @Pos = charindex(@Delimiter,@String)
 set @NextPos = 1

 --Loop while there is still a comma in the String of levels
 while (@pos <>  0)  
 begin
  set @NextString = substring(@String,1,@Pos - 1)

  insert into @ValueTable ( [Value]) Values (@NextString)

  set @String = substring(@String,@pos +1,len(@String))

  set @NextPos = @Pos
  set @pos  = charindex(@Delimiter,@String)
 end

 return
end

so you can get the one by one value as

"Select value from dbo.fnsplit (@values,',')"

openshac · Accepted Answer · 2011-04-07 07:34:57Z

0

I think you mean ServiceId and not ServicesId. In addition the third parameter of OPENXML should not be @ServicesIdCount, it should be a value describing what sort of output you want. Don't think you specify a ROOT node in your XML, so remove that

SELECT * FROM OPENXML(@XmlDocHanle, '/ROOT/Services/ServicesId',@ServicesIdCount)
WITH (ServiceId INT)

should be

SELECT * FROM OPENXML(@XmlDocHanle, '/Services/ServiceId', 1)
WITH (ServiceId INT)

answered Apr 7, 2011 at 7:34

openshac

5,1936 gold badges48 silver badges83 bronze badges

1 Comment

RichardTheKiwi Over a year ago

That will look for ServiceId @attribute in /Services/ServiceId

Collectives™ on Stack Overflow

Sql Server - pass array to procedure

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related