select query with variables not returning correct results

Question

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

I'm not sure what your question is. With a single variable, in is the same as =, so your code is really code = @code. And a single code isn't going to match a list. — Gordon Linoff
– Gordon Linoff, Commented Nov 25, 2016 at 14:42
One is a string, the other is an array/list. IN will compare the whole string, wont separate the values. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 25, 2016 at 14:50

GuidoG · Accepted Answer · 2016-11-25 14:59:28Z

3

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

answered Nov 25, 2016 at 14:59

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select query with variables not returning correct results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related