CODEWITHSUNDEEP
sql-servert-sql
mHelpMe
mHelpMe

Reputation: 6668

select query with variables not returning correct results

So I have the two queries below.

The first query works however in my where condition I've hard coded some values. So in the second query I've put the same values but declared them as variables. However when I run it nothing is returned, which I don't understand?

First Query

declare @fxPair nvarchar(max) = ''
select @fxPair = @fxPair + '[' + Currency + '], ' 
from myTbl  
where DateH = '2016-11-14' and Code in ('ABV', 'ABG')
group by Currency
order by Currency   
set @fxPair = SUBSTRING(@fxPair, 1, len(@fxPair) - 1)
print @fxPair

Second Query

declare @Code nvarchar(10) = 'ABV, ABG'
declare @DateH nvarchar(20) = '2016-11-14'

declare @fxPair nvarchar(max) = ''
select @fxPair = @fxPair + '[' + Currency + '], ' 
from myTbl  
where DateH = @DateH and Code in (@Code)                -- this line doesn't work
group by Currency
order by Currency   
set @fxPair = SUBSTRING(@fxPair, 1, len(@fxPair) - 1)
print @fxPair

Upvotes: 1

Views: 75

Answers (1)

GuidoG
GuidoG

Reputation: 12014

you can try it like this :

declare @code table (code varchar(10))
declare @DateH nvarchar(20) = '2016-11-14'
declare @fxPair nvarchar(max) = ''

insert into @code values ('ABV'), ('ABG')

select @fxPair = @fxPair + '[' + Currency + '], ' 
from   myTbl  
where  DateH = @DateH 
and    Code in (select code from @code)
group by Currency
order by Currency   

set @fxPair = SUBSTRING(@fxPair, 1, len(@fxPair) - 1)
print @fxPair

Upvotes: 3

Related Questions