• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

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
0
PMH4514
Asked:
PMH4514
  • 6
  • 5
  • 3
2 Solutions
 
Daniel WilsonCommented:
>>What's the proper way to do that?

Best would probably be to split the string in the app code ... but ... assuming you want to pass to the stored procedure that way ...

first, you need to add the function fn_split to your DB.  I guess it's made it to MSDN ... http://msdn.microsoft.com/en-us/library/aa496058(SQL.80).aspx

Then your procedure will look something like:

Create Procedure dbo.MyProc
 
@study_id int,
@topic_id nvarchar(1000)
 
 
AS
 
Insert Into MyTable (study_id, topic_id)
(Select @Study_ID, Value from dbo.fn_split(@topic_ID, ','))

Open in new window

0
 
BrandonGalderisiCommented:
I didn't see the fn_split code, but here's one using my split funciton:

Create Procedure dbo.MyProc
 
@study_id int,
@topic_id nvarchar(1000)
 
 
AS
 
Insert Into MyTable (study_id, topic_id)
Select @Study_ID, theValue from [dbo].[fn_DelimitedToTable](@topic_ID, ',')
go

The function code is below.
if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Open in new window

0
 
PMH4514Author Commented:
hmm, both excellent answers.. should I split points?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Daniel WilsonCommented:
sounds good to me!
0
 
BrandonGalderisiCommented:
I'm game:

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

0
 
Daniel WilsonCommented:
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.
0
 
PMH4514Author Commented:
I found it in a zip file on the authors blog:

http://www.sqlmag.com/article/articleid/21071/21071.html
0
 
BrandonGalderisiCommented:
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.
0
 
PMH4514Author Commented:
ok thanks! all good learning material
0
 
PMH4514Author Commented:
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
0
 
PMH4514Author Commented:
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

0
 
BrandonGalderisiCommented:
It's actually contained in the comment in the lower section of the function above too.  That's just how I post it.
0
 
PMH4514Author Commented:
ha! I have no idea how I missed that.. :)
0
 
BrandonGalderisiCommented:
No biggie.  You'll see how useful vw_nums can be if you browse around EE because I use that a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now