CODEWITHSUNDEEP
sql-server
Alexandre
Alexandre

Reputation: 13308

Sql Server - pass array to procedure

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.

Upvotes: 0

Views: 878

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

Upvotes: 3

openshac
openshac

Reputation: 5165

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)

Upvotes: 0

harshagile
harshagile

Reputation: 346

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,',')"

Upvotes: 1

Related Questions