asked on

Dynamic SQL statement passing comma seperated values into table

I have a stored procedure that accepts two input parameters:

@study_id int
@topic_id nvarchar(1000)

topic_id is a comma separated list of ID values coming in from an HTML form.

I have a table study_topic which relates study_id [1:N] topic_id

so for each value in topic_id, I need to insert a new row study_id, topic_id

What's the proper way to do that?

Thanks

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PMH4514

ASKER

hmm, both excellent answers.. should I split points?

Daniel Wilson

sounds good to me!

BrandonGalderisi

I'm game:

But could you update with a reference to fn_split DW. I couldn't find one on that page.

Daniel Wilson

Sorry about that.

http://www.sqlmag.com/article/articleid/21071/21071.html -- the zip file there has it.

But here it is right in the HTML ...
http://www.umachandar.com/technical/SQL2000Scripts/UtilityFns/Main7.htm
I'm thinking this guy was the original author, but am not sure.

PMH4514

ASKER

I found it in a zip file on the authors blog:

http://www.sqlmag.com/article/articleid/21071/21071.html

BrandonGalderisi

Based upon it's age (2001) it predates SQL 2005 and CTEs. Plus, they specifically state that it does explicit looping in something that I read. So the one I provided will no doubt be faster since it uses the looping of the CTE which is very fast.

I would recommend using my function.

PMH4514

ASKER

ok thanks! all good learning material

PMH4514

ASKER

BrandonGalderis - sorry for the delayed followup as I was called to a different project for a while.
I decided to try your version as well, but I have a problem running it. I get this error:

Invalid object name 'dbo.vw_Nums'

Is that something you created that you didn't post code to or is it installed somewhere in SQL Server?

I am using SQL Server 2005 Express

Thanks

PMH4514

ASKER

Nevermind that last comment. I googled vw_Nums and found another post of yours on EE that contained it.

Pasted from the other thread:

create view vw_Nums
as
with
       cte0 as (select 1 as c union all select 1), -- 2
       cte1 as (select 1 as c from cte0 a, cte0 b), -- 4
       cte2 as (select 1 as c from cte1 a, cte1 b), -- 16
       cte3 as (select 1 as c from cte2 a, cte2 b), -- 256
       cte4 as (select 1 as c from cte3 a, cte3 b), -- 65,536
       cte5 as (select 1 as c from cte4 a, cte4 b), -- 4,294,967,296 --four BILLION, not million
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
 
select theValue from [dbo].[fn_DelimitedToTable]('a','|')

Open in new window

BrandonGalderisi

It's actually contained in the comment in the lower section of the function above too. That's just how I post it.

PMH4514

ASKER

ha! I have no idea how I missed that.. :)

BrandonGalderisi

No biggie. You'll see how useful vw_nums can be if you browse around EE because I use that a lot.