SELECT Query - SQL Server Forums

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)

SELECT Query

Author

Topic

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-12-13 : 02:36:23

I have a stored proc and it takes 2 parameter
@LocationIDs varchar(2000)
@MarketIDs varchar(2000)

From my UI i sends more than one locationID and marketID as string
means something like this
LocationIDs = '2,3,4,5,6'
MarketIDs = '3,5,4'

but in my table LocationID and MarketID are integer.

I worte a query like

SELECT * FROM Location
WHERE (LocationID IN (@LocationIDs) OR LocationID = NULL)
and (MarketID IN (@MarketIDs) OR MarketID = NULL)

I works when i pass one location id and marketid
if i sends more than one it wont work

How can i solve this prob
The one problem is that i can use EXEC becoz my client doesn't want it.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 02:55:02

SELECT * FROM Location
WHERE ('%'+@LocationIDs+'%' like '%'+cast(LocationID as varchar(5))+'%' OR LocationID IS NULL)
and ('%'+@MarketIDs+'%' like '%'+cast(MarketID as varchar(5))+'%' OR MarketID IS NULL)

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-13 : 02:56:42

Look at this article
http://www.sqlteam.com/item.asp?ItemID=11499

Duane.

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-12-13 : 03:18:15

thanks
but it works for locationID not for MarketID

i ran the query like this
proc sp_loc '2,3','2'

Its not filtering the data by marketID

when i write like
proc sp_loc '2,3' its working fine

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 03:20:15

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=WHERE+IN+@MyCSV

Kristen

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-12-13 : 03:24:41

thanks to all

My first approch working fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources