Declaring and using SQL variables in an Advanced Query

Pedro Gonçalves

Staff

Solved

Declaring and using SQL variables in an Advanced Query

Question

Hi,

I'm trying to declare and use a SQL variable into an advanced query. The problem is, when testing it, I get the following error: «Invalid SQL - Unknown MyString parameter in Query».

Any ideas? Thanks,
Pedro Gonçalves

29 Apr 2008

Tiago Simões

Staff

Solution

Hi Pedro,

I think you can overcome that by:
1 - Declaring an input parameter to the advanced query e.g. SQLVariable1
2 - Setting the "Expand Inline" property to "Yes"
3 - Passing a static string with that variable "@mySQLVariable"

Hope this helps,

Tiago Simões

04 May 2008

2 replies

Last reply 20 Jan 2018

Show thread

Hide thread

Pedro Gonçalves

Staff

Hi Tiago. That did work. Thanks!

07 May 2008

Devu Mani

Tiago Simões wrote:

Hi Tiago,

I couldn't find expand Inline where is it?? Can you please help me??

20 Jan 2018

kota

I am trying to do something like this:

Advanced query with Imput parameter : CountryNames with value "@CountryNames"
and CountryCodeListForAdvQuery with value "( 'CA','US' )", both inline.

If I just do

SELECT {COUNTRY}.[CountryName]
FROM {COUNTRY}
WHERE {COUNTRY}.[CountryCode] IN @CountryCodeListForAdvQuery

It works and I get 2 countries.

But this one doesn't return any rows.

DECLARE @CountryNames varchar (200)

SET @CountryNames = ''

SELECT @CountryNames =

coalesce (case when @CountryNames = ''

then {COUNTRY}.[CountryName]

else @CountryNames + ',' + {COUNTRY}.[CountryName]

end

,'')

FROM {COUNTRY}

WHERE {COUNTRY}.[CountryCode] IN @CountryCodeListForAdvQuery

Am I missing something ?

21 Feb 2014

André Vieira

Staff

Hi ksah,

I'd say you can't declare @CountryNames inside the Advanced Query.
What are you trying to achieve here? Is it a comma-separated list of country names?
If so look at the following page: https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

22 Feb 2014

kota

Thanks Andre, Yes indeed it was comma separated country names. I posted the comment on this post because there was already some discussion about using SQL variable inside advacned query. If you look at the Tiago Simões comment, he have mentioned a way to use SQL variable in advanced query.

24 Feb 2014

André Vieira

Staff

Hi ksha,

I understand.

Were you able to create the query to ouput the comma-separated list of country names?

24 Feb 2014

kota

I haven't tried the link that you have sent until now. For now I am using

SELECT {COUNTRY}.[CountryName] FROM {COUNTRY} WHERE {COUNTRY}.[CountryCode] IN @CountryCodeListForAdvQuery

with the loop.

24 Feb 2014

kota

So I have tried your link and it worked.Thanks. So We can not declare inside sql query ? Is this correct ?

24 Feb 2014

André Vieira

Staff

Not that I know of. If you declare @Var then you would need an input to that query called Var otherwise the code won't compile.

24 Feb 2014

Remco Dekkinga

MVP

Hi guys,

Sorry for the reply on an old case, but the first reply from Tiago Simoes on this case works for declarations in an advanced query.

Kind regards,

Remco

11 Jan 2017

Tiago Neves

Hi Devu,

I just bumped into this post and saw your question... You'll find Expand Inline here:

Next time please start a new post instead of necromancing an old one, you'll probably get a faster answer also. I marked Tiago Simões post as Solution.

27 Jan 2018

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines