Solved

Dynamic SQL statement passing comma seperated values into table

Posted on 2008-10-07
14
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 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