SP to Insert using Split function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP to Insert using Split function

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-06-22 : 15:51:54

I have a UDF that splits strings at when a comma is found. I want to leverage this UDF in a SP to be able to insert multiple records from a single parameter. For exmaple, I have three parameters @a @b @c. I want to allow user to enter @a = 1,2,3 @b=a,b,c @c=red,white,blue. If this is entered in the store procedure parameters I want to insert the records such that the following table is created:

Col1 Col2 Col3
1 a red
2 b white
3 c blue

Can anyone think of a way that I can implement this functionality?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-22 : 16:31:08

Does your string splitter also provide an item number? If it does, you can join on the Item number after calling the splitter function three times, once for each string.

The string splitter that I use most often is Jeff Moden's splitter function (code here in Figure 21: http://www.sqlservercentral.com/articles/Tally+Table/72993/ ) If you are using that, you would do something like this:

DECLARE @s1 VARCHAR(8000), @s2 VARCHAR(8000), @s3 VARCHAR(8000);
SET @s1 = '1,2,3';
SET @s2 = 'a,b,c';
SET @s3 = 'red,white,blue';

SELECT
	a.ItemNumber,
	a.Item,
	b.Item,
	c.Item
FROM
	dbo.DelimitedSplit8K(@s1,',') a
	INNER JOIN dbo.DelimitedSplit8K(@s2,',') b
		ON a.ItemNumber = b.ItemNumber
	INNER JOIN dbo.DelimitedSplit8K(@s3,',') c
		ON a.ItemNumber = c.ItemNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 23:33:00

why cant you implement it as a single xml parameter with below structure so each value set can passed together?


<ValueList>
<Values>
<a>1</a>
<b>a</b>
<c>red</c>
</Values>
<Values>
<a>2</a>
<b>b</b>
<c>white</c>
</Values>
<Values>
<a>3</a>
<b>c</b>
<c> blue</c>
</Values>
</ValueList>

then use xml function like nodes(),value() inside to shred out required data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources