Solved

Dynamic SQL statement passing comma seperated values into table

Posted on 2008-10-07
14
514 Views
Last Modified: 2012-05-05
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
Comment
Question by:PMH4514
  • 6
  • 5
  • 3
14 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 65 total points
ID: 22660279
>>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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 60 total points
ID: 22660449
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
 

Author Comment

by:PMH4514
ID: 22662243
hmm, both excellent answers.. should I split points?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22662264
sounds good to me!
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22662337
I'm game:

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

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22662424
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
 

Author Comment

by:PMH4514
ID: 22662426
I found it in a zip file on the authors blog:

http://www.sqlmag.com/article/articleid/21071/21071.html
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22663129
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
 

Author Comment

by:PMH4514
ID: 22663840
ok thanks! all good learning material
0
 

Author Comment

by:PMH4514
ID: 22730521
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
 

Author Comment

by:PMH4514
ID: 22730534
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22730737
It's actually contained in the comment in the lower section of the function above too.  That's just how I post it.
0
 

Author Comment

by:PMH4514
ID: 22734341
ha! I have no idea how I missed that.. :)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22734415
No biggie.  You'll see how useful vw_nums can be if you browse around EE because I use that a lot.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now