Using: in (@variable) - 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)

Using: in (@variable)

Author

Topic

fchamis
Starting Member

2 Posts

Posted - 2004-06-08 : 23:07:02

Hello folks!

How can i make this work:

declare @valores nvarchar(50)
set @valores = '2,3,4'
select * from DadoFixo WHERE IDDadoFixo in (@valores)

Thanks a lot!
Fernando Chamis - Webcore - Brasil

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-08 : 23:15:59

declare
@valores nvarchar(50),
@sql nvarchar(255)

set @valores = '2,3,4'
set @sql = 'select * from DadoFixo WHERE IDDadoFixo in (' + @valores + ')'
print @sql
exec(@sql)

Or, you can create a table function to parse the list and just join to that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-08 : 23:45:00

Two additional solutions for the in (@variable) problem:

solution 1:
http://www.4guysfromrolla.com/webtech/031004-1.shtml

solution 2:
declare @valores nvarchar(50)
set @valores = ',2,3,4,'
select * from DadoFixo WHERE charindex(',' + IDDadoFixo + ',', @valores) > 0

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-09 : 00:31:42

There you go again Corey. Way cool!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-09 : 01:36:17

That's kewl :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

fchamis
Starting Member

2 Posts

Posted - 2004-06-09 : 20:27:17

wow!

Thanks a lot. A very good solutions.

Regards,
Fernando Chamis.