sp question about passing comma-delimited string

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

SQL Server Development (2000)

sp question about passing comma-delimited string

Author

Topic

rswoods
Starting Member

4 Posts

Posted - 2006-01-26 : 22:32:58

Hi,

This is probably a dumb question, but..

I am trying to create a stored proc similar to the following:

===========================================
CREATE PROCEDURE getEvents @eventlist varchar(20)
AS
begin

select foo1, foo2 from events
where eventClass in (@eventlist)

end
GO
==========================================

@eventlist is a comma-delimited list of integers, like '3,5,6,7'. This does not work because eventClass is an int, and mssql throws an error about converting a varchar() to an int, which is understandable. Is there a way to make this work without having to build the sql statement in the proc as an nvarchar() then run "exec sp_executesql @sql"? That does work, but it doesn't seem very efficient.

Thanks,
rsw

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-26 : 22:49:49

This is answered in the FAQ. Look in the Stickey post in the "New to SQL Server" forum.

rswoods
Starting Member

4 Posts

Posted - 2006-01-26 : 23:49:14

Thanks. The UDF in the FAQ works well, is ~20% faster than exec'ing the constructed sql.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 01:23:38

Other method would be

select foo1, foo2 from events
where ','+@eventlist+',' like '%,'+cast(eventClass as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

rswoods
Starting Member

4 Posts

Posted - 2006-01-27 : 06:57:09

Thank you! *Much* better performance than the UDF in the FAQ.

rsw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources