sproc with dynamic "where in" clause

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)

sproc with dynamic "where in" clause

Author

Topic

sherpa99
Starting Member

20 Posts

Posted - 2005-03-23 : 17:00:13

I need to use a "where in" clause in my stored procedure. The where in clause could be numerous values separated by commas -- how do I pass in the parameters and make it work?

I think I'm close -- but obviously this is not working yet.

Exec email_membersCountries_get 'eu,na'

CREATE PROCEDURE email_membersCountries_get

@continentsParam nvarchar(50)

AS

SELECT DISTINCT
a.countryID,
a.countryCD,
a.countryDesc,
a.sortOrder,
b.continentID,
b.continentCD
FROM
lookup_countries a
INNER JOIN lookup_continents b
ON a.continentID = b.continentID
INNER JOIN members c
ON a.countryCD = c.country

WHERE
b.continentID IN (@continentsParam)

ORDER BY
b.continentID,
a.sortOrder,
a.countryDesc

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-03-23 : 17:36:57

search in sql team for passing CSV data to the stored procedures..

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

http://www.sqlteam.com/item.asp?ItemID=637

sherpa99
Starting Member

20 Posts

Posted - 2005-03-24 : 14:38:25

perfect ... excellent and easy solution

I implemented the user-defined-function named "CsvToInt" and detailed in this article.

http://www.sqlteam.com/item.asp?ItemID=11499

Then called it from within my original stored procedure.

SELECT DISTINCT
a.countryID,
a.countryCD,
a.countryDesc,
a.sortOrder,
b.continentID,
b.continentCD
FROM
lookup_countries a
INNER JOIN lookup_continents b
ON a.continentID = b.continentID
INNER JOIN members c
ON a.countryCD = c.country

WHERE
b.continentID IN (Select IntValue from dbo.CsvToInt(@continentsParam) )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources