How To Use The 'in' Statement in Parameterized SQL Queries

taylor

How can I successfully use the 'in' statement with a parameterized SQL query? Our goal is to do something like select * from TableName where FieldName in (@variable). In this scenario @variable would be a list of strings instead of a single string.

Kathryn_Decisions

There may be several ways to do this, depending on what you are using as your SQL database. Different databases will use a different wildcard character. For example, Oracle will use a colon (:) character, and snowflake will use a question mark (?). Some also support lists as inputs to parameterized queries, while others do not. 

If you are using a database that supports lists, this can be done by editing your parameter (either "add", or the pencil icon next to an existing parameter under the "Input Parameters" section of the database integration popup), and selecting "Is List". 

If you are using a database that does not support lists, this can still be done, but it does require a bit more configuration. Within your Decisions flow, you will need to convert your list of strings into a single string separated by a comma, in a Join Strings step. Use the output of this as the input of your parameter, adjust your query to split the string based on comma. The resulting query would look something like this:

SELECT *
FROM table_name
WHERE field_name in 
(SELECT value FROM STRING_SPLIT(@variable,','));

• Parameterized Queries: https://documentation.decisions.com/docs/parameterized-queries