SQL Where in Clause

  

Hi all,


I am trying to format a where clause that looks something like the following


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

This is so that if the @names variable is empty it still populates the whole list.


However, if the @names = "'James', 'John'", then I get the error incorrect syntax near ','. 

If i remove the ' or @Names = '' ' the code works, how can I include that line and still get the code to work?

It also works if @Names="'James'" or @Names = "'John'"


The expand inline condition is set to yes.


Thanks,

James

Hello James 

Is the @Names variable of data type text? E.g. is it @Names = "James,John,james,Henry", etc?
Regards,

Ângelo

Ângelo Sousa wrote:

Hello James 

Is the @Names variable of data type text? E.g. is it @Names = "James,John,james,Henry", etc?
Regards,

Ângelo

Hi,


Yes it is. However as some of the first names have spaces each word has single quotes around it


Hello James

I think OutSystems gets a little confused when dealing with quotation marks. Funny enough, I'm dealing with a similar issue right now, as I'm sending a string with some quotation marks to a REST API, and in ServiceCenter, I see some of the quotation marks are being automatically interpreted as \ on the JSON request.

Since your code works if you remove the or @Names='' part, I would suggest check if the @Names variable is empty before you call the SQL node. If it is, you just fetch all data to populate, if not, the flow should continue to the SQL node without the or @Names = '' part. I understand it's a bit of a workaround, but it might work


Regards,

Ângelo

Hello James,

The reason for the error is the fact that the parameter is expanded as part of the SQL. This gives the following SQL code:

... Where {Name}.[FirstName] in ('James', 'John') or 'James', 'John' = ''

This is obviously incorrect SQL-code. 

A possible solution is to include the full WHERE-clause only when @Names has a value, e.g.
@NamesWhere = "Where {Name}.[FirstName] in ('James', 'John')"

You can also use a second parameters with the names that's not expanded, e.g.:
Where {Name}.[FirstName] in (@NamesExpanded) or @NamesNotExpanded = ''