Advanced SQL Query in clause

  

Hi all, 


I am trying to do an advanced SQL Query with an in clause. The clause looks something like:

Where {Name}.[FirstName] in (@Names).

The @Names variable looks something like: 

@Names= "Fred, Bob, James"


This doesn't seem to work and I am not sure how to format the @Names variable in order to get it to work.

The following does work: 

Where {Name}.[FirstName] in ('Fred', 'Bob', 'James')

But when you format @Names="'Fred', 'Bob', 'James'" it does not work.

Also it does work when @Names="Fred" or @Names="James" etc.


I have also tried to do it based on id. Like:


Where {Name}.[Id] in (@NameIdentifier).

Where @NameIdentifier is for example "12, 43". However I get an error saying it failed to convert "12,43" to type int.


Any ideas?


Thanks,

James


Solution

Hi,

For that to work you will need to set the @Names's Expand Inline parameter to 'Yes', as seen below.


This parameter is required so that the input variable is evaluated as SQL and not as just a string.

Cheers,

João Mateus

Solution

João Mateus wrote:

Hi,

For that to work you will need to set the @Names's Expand Inline parameter to 'Yes', as seen below.


This parameter is required so that the input variable is evaluated as SQL and not as just a string.

Cheers,

João Mateus


Hi Joao, Thank you for your response.


I have done this, however when using a test value (@Names = "James, Fred") I get the error 'Incorrect Syntax near ","' 


Thanks,

James

Hi James,

Probably you'll have to add single quotes to your values list.

" 'James' , 'Fred' " 

João Heleno wrote:

Hi James,

Probably you'll have to add single quotes to your values list.

" 'James' , 'Fred' " 


Hi Jaoa, 


That was the issue, thank you for your help!

When I use single quotes, for some reason it doubles it: "AND activity = 'NB'"


AND activity = ' 'NB' '


why is that?