question

my favourite avatar image
my favourite asked

How to pass multiple comma separated values of a single xml parameter(wellid) to a storedprocedure in SQLSERVER2005

I have something like this below in XML. 1,4,8,5,10.... How I can pass this multiple values to my existing storedprocedure which was accepting single value earlier.I am not allowed to create any table or any new parameter.Please suggest as soon as possible.I will highly appreciate your input.
sp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
This is one of the [definitive works on this][1]. You just have to create a numbers, or tally, table and you're off. If you pass it as XML, you'll have to do XML parsing to through OPENXML. Passing it as just a string will be easier. [1]: http://www.sqlservercentral.com/articles/T-SQL/62867/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Echoed by Erland Sommarskog, who covers a variety of string splitting methods - http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum
1 Like 1 ·
Oleg avatar image
Oleg answered
The link which @Grant Fritchey posted in his answer points to the best pure T-SQL method of splitting delimited values writen by Jeff Moden, who is by the way the winner of this year [Exceptional DBA Award][1]. Jeff's method is the one to use despite the fact that it necessitates a creation of the Tally table and the table valued function to actually split the values. There is another quick and dirty solution which will work fast because from the question definition it looks like you just need a way to generate a rows of values from the given delimited list. Perhaps you have a procedure which needs to retrieve some data from the data store for the specified list of wells passed into the procedure as a parameter. For example, suppose you have a procedure like this: create proc dbo.SomeProcName ( @list xml ) as begin; set nocount on; -- select some data from the WellInfo table for the -- specified list of wells passed into the procedure -- as xml in the form of 1,2,3,4,5,6 set nocount off; end; go If the list of wells were passed into this procedure in a little bit different form then you could just use the T-SQL ability to query xml data directly. In other words, if your @list parameter had the value in this form: 1 2 3 etc then the life would be really easy. The good news is that if you replace every occurence of the comma with close and open of the node then it would come to look like the shape shown above. Then you could query it straight. For example, set @list = replace(cast(@list as varchar(max)), ',', ' '); -- and now select item.value('.[1]', 'int') WellId from @xml.nodes('wellid') R(item); The select above gives you the results in the form of One column named WellId and as many rows as there are items in your original list. You can then use it any way you like, for example, ;with wells as ( select item.value('.[1]', 'int') WellId from @list.nodes('wellid') R(item) ) select * from dbo.YourWellsTable w inner join wells on w.WellId = wells.WellId -- or even this way: select * from dbo.YourWellsTable where WellId in ( select item.value('.[1]', 'int') WellId from @list.nodes('wellid') R(item) ); Oleg [1]: http://www.exceptionaldba.com/?utm_source=bradmcgehee&utm_medium=email&utm_content=xdba_winner201109&utm_campaign=exceptionaldba
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.