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

Transact-SQL (2008)

Multi value variable

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-08 : 08:03:27

hi

I want to list out all results from tblB based on the values which assigned to variable @x from tblA. How should i go about it?

In tblA field1 i have values such as car, bus, train

Declare @x as nvarchar(200)
select @x = field1 from tblA

Select * from tblB
where field2 like '%' + @x + '%'

I don't seems to get all the result set? thanks

Ancy
Starting Member

23 Posts

Posted - 2010-09-08 : 08:27:44

To get the whole resultset, do not use a variable as only the last value from the query will be stored in the variable.

You will need to loop through all the values in tblA and repeat the query for each value

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-08 : 09:54:25

hi

I am still not able to get the result based on the while loop.

Any suggestions? Thanks

declare @x nvarchar(200)
declare @RowNum int
set @RowNum = 0
WHILE @RowNum < (select Count(field1) from tblA)
BEGIN
set @RowNum = @RowNum + 1
select @x = field1 from tblA
Select * from dbo.tblB
where field2 like '%' + @x + '%'
END

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-08 : 10:42:03

hi

I have managed to use cross appply to solve this

Select * from tblB
cross apply
(
select field1 from tblA
where field2 like '%' + field1 + '%'
) as ans

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-09-10 : 01:12:41

Is LIKE necessary? Or are you simply trying to join the two tables?

Perhaps?


Select * 
from   tblB
where  Field2 in (select Field1 from tblA)

select *
from   tblB b
join   tblA a on 
       b.Field2 = a.Field1

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-10 : 02:22:23

Like is necessary because of some biz logic. Anyway, the cross apply does solve the problem. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources