Declaring and using multiple values as a variable

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

Transact-SQL (2008)

Declaring and using multiple values as a variable

Author

Topic

MTech8
Starting Member

10 Posts

Posted - 2011-07-12 : 10:11:53

I want to be able to put a list of productID's into a variable and be able to reuse it throughout my query. I'm able to do that for 1 productID, but need help to figure out how to do that for multiple ones.

ie: (this works for single productID)
DECLARE @productID as varchar(100)
SET @productID = '12345'

SELECT * FROM TABLEA WHERE productID = @productID
SELECT * FROM TABLEB WHERE productID = @productID

However, I replace:
SET @productID = '12345, 67890, 23456'
SELECT * FROM TABLEA WHERE productID in (@productID)

I get an error that states that I "Must decalre the scalar variable '@productID'

How do I fix this problem?

Thanks!!

MTech8
Starting Member

10 Posts

Posted - 2011-07-12 : 10:19:23

Also.. I'm not using this in Stored Procedure. Just need to be able to run the query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-12 : 10:43:46

1 Use split function and join with the actual table
2 use the below code
where ','+@productID+',' like '%,'+cast(productID as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-12 : 10:45:54

But then don't use spaces in your SET @productID...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources