pass comma seperated values from a field into SP

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 2000 Forums

Transact-SQL (2000)

pass comma seperated values from a field into SP

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-12 : 14:46:14

I have a table with a column which has comma sepeared list

create table list (list_of_values char (200))

insert into list values ('A,B,C,D,E,F')
insert into list values ('G,H, Y, Z')

Now I need to pass these each value into a stored procedure
as @list_of_value. So there are 10 values in the above example.

How can I do this. I know I can create a UDF to create a table.
But how to do that or with SubString but again not sure on how to get the result.

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 14:53:06

Check if this helps: http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-12 : 15:14:32

thanks but when i create the funtion and the table both in my database
and try this

select fnParseArray(list_of_values) from list I get an error

Server: Msg 195, Level 15, State 10, Line 1
'fnParseArray' is not a recognized function name.

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-12 : 15:28:00

Here is another example

How do I use this in a regular table and what if there are multiple parsers like | and , Then what.
How do I test this for multiple rows.

--declare the list of Cities
DECLARE @CityList varchar(8000)
SET @CityList = 'Milwaukee|Chicago|New York|Seattle|San Francisco'

--declare the delimeter between each City
DECLARE @Delimeter char(1)
SET @Delimeter = '|'

--Parse the string and insert each city into the @tblCity table
DECLARE @tblCity TABLE(City varchar(50))
DECLARE @City varchar(50)
DECLARE @StartPos int, @Length int
WHILE LEN(@CityList) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @CityList)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@CityList) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @City = SUBSTRING(@CityList, 1, @StartPos - 1)
SET @CityList = SUBSTRING(@CityList, @StartPos + 1, LEN(@CityList) - @StartPos)
END
ELSE
BEGIN
SET @City = @CityList
SET @CityList = ''
END
INSERT @tblCity (City) VALUES(@City)
END

--Show all Cities in the @tblCity table
SELECT * FROM @tblCity

Ashley Rhodes

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-12 : 15:42:26

jeff did a pretty good article on the subject:
http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 16:42:30

What do you mean by using it in regular table. Can you explain what you are trying to do?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-12 : 17:08:38

how do i used your function on this table

create table list (list_of_values char (200))

insert into list values ('A,B,C,D,E,F')
insert into list values ('G,H, Y, Z')

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 17:14:42

How do you want the result? What do you need to do with the result. The function code I provided returns a table type. So you cannot use it over a table.
There is also a stored proc version of the code where you can parse the values out. So if you need to do any inserts/updates/deletes for each value you can do it via proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-13 : 00:09:51

1 http://www.datamodel.org/NormalizationRules.html
2 http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-13 : 08:29:06

Don't forget to put the owner name in the function call
select dbo.fnParseArray(list_of_values). That's why SQL doesn't recognize it.

Jim

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-13 : 08:52:05

Erland also treats this thoroughly:

http://www.sommarskog.se/arrays-in-sql.html

elsasoft.org

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-13 : 13:42:20

i did put the name of the owner. I still get the same error.

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-07-13 : 13:43:36

Yes I want to see this as a table

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 13:47:55

Can you show us a sample of how your input will be and how you expect the output to be?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources