Using a variable in where 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

General SQL Server Forums

New to SQL Server Programming

Using a variable in where clause

Author

Topic

Sean Frost
Starting Member

19 Posts

Posted - 2009-12-31 : 12:14:38

I am trying to figure out how to use a variable that contains a comma-delimited list in the where clause of a query. The example below shows what I am trying to do.

declare @tbl table (ticker varchar(32));
insert into @tbl values ('IBM');
insert into @tbl values ('GOOG');
insert into @tbl values ('MSFT');

declare @tickers varchar(max);
set @tickers = 'IBM';

-- this works when I have only one ticker;
select * from @tbl where ticker in (@tickers);

-- now two tickers
set @tickers = '''IBM'',''GOOG''';

-- see what is in the variable.
select @tickers as Tickers;

-- but select does not return anything
select * from @tbl where ticker in (@tickers);

-- this works, of course.
select * from @tbl where ticker in ('IBM','GOOG');

Only alternatives I can think of are
a) using dynamic SQL
b) splitting and putting the comma-separated string into a table variable and joining with that table.

But, this method seems so elegant and brilliant, yet it doesn't work. (So obviously my idea is not that elegant, nor is it brilliant!)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-31 : 12:41:16

select * from @tbl where @tickers like '%'+ticker+'%'

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

Sean Frost
Starting Member

19 Posts

Posted - 2009-12-31 : 13:09:24

webfred, thanks! That works.

I modified it slightly as follows:

select * from @tbl where ','+@tickers+',' like '%,'+ticker+',%'

This will let me return only PMC Sierra (ticker: PMCS) and not return Phillip Morris (ticker: PM) when looking for ticker PMCS.

Still, I claim that my original idea is brilliant - Microsoft hasn't yet caught up to my level of highly sophisticated thinking.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-31 : 13:44:08

You're really not the first person with that brilliant idea

In most cases a function is needed to return a table with splitted values so the function can be used in a join like a table.

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