Stored Procedure With Comma Separated values

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

Transact-SQL (2005)

Stored Procedure With Comma Separated values

Author

Topic

murthybsnvsn
Starting Member

3 Posts

Posted - 2007-06-25 : 15:35:34

Hi All,

Iam new to stored procedures,

Can u please help me in this task:

I have To pass two values which are comma separated in my stored procedure.

My Scenario is:

I have one System Table

it consists of columns : SystemID, SystemName

I have one Symptoms Table

it consists of columns : SymptomID, SymptomName, SystemID

I have one Templates Table

it consists of columns : TemplateID, TemplateName, SymptomName

Now i have to get Templates Realted to a symptom related to systems which we have to pass as input parameters as Comma Separated Values:

Like I have to pass SystemIDs 1,2

I have to get Symptoms related to those systems and templated related to symptoms,

But I know by passing one system id, i can get the values of a particular system but i have to pass the more than one value and need to get the result.

Please help me in this regard.

Thanks in advance

Cheers
Murthy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 15:43:02

Search SQLTeam for "Split string", "csvtable".

Peter Larsson
Helsingborg, Sweden

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-25 : 17:31:37

[code]
Create function [dbo].[ParseIntArray](@in varchar(8000))
returns @out table (i int)
as
begin
declare @idx int
select @idx = 1
,@in = ',' + @in + ','
while @idx > 0 and @idx < len(@in)
begin
insert @out (i)
select i
from (select substring(@in, @idx+1, charindex(',', @in, @idx+1)-@idx-1) i) a

--make sure it's an int
where i not like '%[^0-9]%'
and len(i) <= 10 --this ones not fool proof but...eh

--reset @idx to next delimiter
set @idx = charindex(',',@in,@idx+1)
end
return
end
[/code]
I use this table valued function to split the multiple values in a sting into a table with integer column. Then in the stored proc, you can use
select * from tableA where systemid in
(
select * from dbo.ParseIntArray('1,2,3')
)

Hope this helps.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 01:30:24

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-06-26 : 02:49:12

so many examples here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://sqlteam.com/forums/topic.asp?TOPIC_ID=67203
http://sqlteam.com/forums/topic.asp?TOPIC_ID=76797

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources