Select Statement - 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 Statement

Author

Topic

sant99
Starting Member

1 Post

Posted - 2005-12-05 : 07:35:43

Hi,

I want to use the following in a procedure.
Case 1 works but Case 2 fails.

------Case 1------------
declare @l_loc varchar (20)

set @l_loc = 'W1'

select locationcode, locationname
from location_master
where locationcode in ( @l_loc)

-- This gives me the right output
-------------------------------
----- Case 2------------------

declare @l_loc varchar (20)

set @l_loc = 'W1' + ',' +'W2'

select locationcode, locationname
from location_master
where locationcode in ( @l_loc)

-- No out put is given. What could be the problem?

Thanks in advance
Santosh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-05 : 07:40:20

You need to use Dynamic SQL

declare @l_loc varchar (20)

set @l_loc = 'W1' + ',' +'W2'

EXEC('select locationcode, locationname
from location_master
where locationcode in ('+ @l_loc+')')

or

select locationcode, locationname
from location_master
where '%'+@l_loc+'%' like '%'+locationcode+'%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources