Passing string variables to a Select don't work..why? 1

[XaRz]

Hi all.

I've a dataset select like this:

DECLARE @reprcodistr VARCHAR(1000) 
SELECT     @reprcodistr = COALESCE (@reprcodistr, '') +
FilteredHCG_CodigoRepresentante.hcg_codigo + ','
FROM FilteredSystemUser INNER  
JOIN FilteredBusinessUnit ON 
FilteredSystemUser.businessunitid = 
FilteredBusinessUnit.businessunitid INNER JOIN
FilteredHCG_CodigoRepresentante ON 
FilteredSystemUser.systemuserid = 
FilteredHCG_CodigoRepresentante.ownerid
Where 
FilteredSystemUser.fullname in (@noms)
SELECT @reprcodistr AS reprcodi

The variable @noms has the value 'table','potato'

If I set the line FilteredSystemUser.fullname in ('table','potato') the select is working fine.

but using a report parameter like the expression:
FilteredSystemUser.fullname in (@noms) don't.
What I'm missing??

thanks for you help in advance.

 

[ousoonerjoe]

the variable is being treated as a literal. When you pass 'table','potato' through the variable, it is looking at it as one item, not a list of items. The comma in your string is not acting as a separator like you'd hoped. You will need to approach this from a different direction to get the results you are looking for.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[XaRz]

Hi ousoonerjoe,

Perhaps I must try to use a multivarible report variable?
I've seen one article referencing it.


Thanks for the tip it makes a lot of sense.