Stored Procedure Parameter and the IN Keyword

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

Transact-SQL (2000)

Stored Procedure Parameter and the IN Keyword

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-21 : 09:39:33

Don writes "Hi - I am having an issue where I am trying to pass in a set of values for the IN the keyword for example:

WHERE State IN ('CA','NY','KS')

I would like to pass this in dynamically using a stored procedure parameter:

@StateCodes Varchar(50)

When I pass this in using ADO.NET in a stored procedure parameter, it looks like it should for T-SQL to be able to handle it properly e.g. ('CA','NY','KS')

Do I need to pass it in differently? I have tried escaping with an additional apostrophe as I have seen in some other posts but that does not work. Any help is appreciated. Thanks!"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 10:22:42

Take a look at these links for how to do this...

http://www.sql-server-performance.com/mm_list_random_values.asp
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/arrays-in-sql.html

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-21 : 11:05:56

or

where ','+@StateCodes+',' like '%,'+state+',%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources