Stored procedure input variables

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

General SQL Server Forums

New to SQL Server Programming

Stored procedure input variables

Author

Topic

thampw
Starting Member

7 Posts

Posted - 2006-02-09 : 22:51:45

Hi,

I want to convert a SQL query as shown below into a stored procedure:


select name
from namelist
where town in ('A','B','D')

If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 23:12:40

see here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6134

----------------------------------
'KH'
everything that has a beginning has an end

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-09 : 23:12:55

Hi,
You can pass a csv string as an input parameter and use split function to convert csv to table.
and use the output in your in clause

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

select name
from namelist
where town in (select data from dbo.split(@namecsvlist))

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-09 : 23:14:47

specify separator as well..
select name
from namelist
where town in (select data from dbo.split(@namecsvlist,','))

thampw
Starting Member

7 Posts

Posted - 2006-02-09 : 23:35:38

Thanks guys for the quick response.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-10 : 07:53:33

Also refer where in @MYCSV here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail