selecting using a string - 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)

selecting using a string

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-28 : 18:41:57

DECLARE @test varchar(200)
SET @test='asc131458,ret131466,fgt131474,uti131482,yup131490'

I have a variable which stored all the emp_id from tbl_employee table and I want to query all the emp_ids from the string stored above.Something like

select * from tbl_employee_details where emp_id in(@test).

Iam not getting the records needed.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-08-28 : 23:06:23

hi sqllearner

correct me if I'm wrong (not sitting in front of SQL Server at the moment) but doesn't your string need to enquote the values?

ie

SET @test = '''' + 'asc131458' + '''' + ',' + '''' + 'ret131466' + ''''

and then you'd need to use dynamic sql to run the query.

Basically what I'm saying is that you can't just pass in a string with commas in it - it needs to be strings separated by commas, eg

where X in ('bla','bla2')

rather than

where X in ('bla,bla2')

etc

Then you'll have to run a dynamic SQL query

eg SET @SQL = 'select * from tbl_employee_details where emp_id in(' + @TEST + ')'
exec(@SQL)  etc etc

(again - sorry not in front of SQL Server for specifics)

Alternatively, if you have several values as separate varchars, you can do

select * from tbl_employee_details where emp_id in (@testa, @testb)

etc
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources