Version 9.3 / Query Business Object using IN (@Parameter)

Hi,

 

I'm populationg a grid with the following bo/Query:

 

SELECT Company.CAMPAIGN_YN, Company.RAISON_SOCIALE,

FROM Company, People

WHERE

Company.SIRET=People.SIRET 

and

left(Company.CP,2) IN (@memDpts).

 

The local variable memDpts is a memo populated using a List field on the form and is bound with @memDpts.

This work fine only if @memDpts is a single value ("65" for example).

What syntax, type ,...  should I use to make it work with a list (really using the IN), I tried many things (add quotes , commas, double quotes,...), but I can't fix it.

Thanks for help.

 

 

Tagged:

Comments

  • I never got IN clauses to work well when the are in put parameters.  (They work fine for constants or in-line code.)

     

    The only way I've sort of gotten around this is to wrap the whole SQL inside an EXEC where so the string can use + to build up the entire query as desired.  I've not gotten that to work very well very often, and I seem to lose the ability to use it on grids as the fields are no longer "available".  (Or I'm doing something wrong, which is quite possible.)

     

    I believe Jerome has suggested elsewhere (here and/or his web site) to insert the keys / folders / values into a temporary table and join or used that table in your IN clause for your list.  (Actually this may resolve similar issues I've not looped back to yet ...)

  • Pierre,

     

    Take a look at the dbo.pudf_stringlist_to_table function that ships with MBPM:

     

    ... AND LEFT(Company.CP, 2) IN (SELECT Item FROM dbo.pudf_stringlist_to_table(@memDpts, ','))

     

    Assuming that @memDpts is a comma separated list of companies.

     

    Hope this helps,

     

    Nils.