topic Re: Any ideas on how to pass a string to a stored procedure for an IN clause? in Pipelines

Any ideas on how to pass a string to a stored procedure for an IN clause?

arpost — Wed, 11 Oct 2023 15:33:49 GMT

Greetings, all. I have an array/list of values I want to pass to a stored procedure in a data warehouse and use the values in an IN statement. I can't figure out how to make this work, however, and am hoping someone can assist.

Here's an example of the list:

'ABC','DEF','GHI'

And here's a sample of what the sproc script could look like where @List is the parameter for the sproc:

SELECT Top 10 * FROM Table WHERE Column1 in (@List)

When applied, the executed script would look like so:

SELECT Top 10 *

FROM Table

WHERE Column1 in ('ABC','DEF','GHI')

Any ideas on how I can convert the string into a useable list on the warehouse side of things?

Re: Any ideas on how to pass a string to a stored procedure for an IN clause?

Anonymous — Wed, 11 Oct 2023 17:28:19 GMT

Hi arpost ,
Thanks for using Fabric Community.
Yes you can convert the string into a useable list in the Fabric Warehouse. I have created a repro of the scenario and was successful in executing the stored procedure. Below are the steps I followed along with the screenshots:

1) I have a table named FabricDemo in my warehouse which has a column named "Code" .

2) I have created a stored procedure named 'infunc3' as follows:

Here we are passing our input as string and using STRING_SPLIT() function to split the input string.

3) After creating the stored procedure, I executed the stored procedure by giving the column values as follows and was successful in getting the records with 'Code' column as 'DNK', 'FIN' and 'ECU'.

Hope this helps. Please let me know if you have any further queries.

Re: Any ideas on how to pass a string to a stored procedure for an IN clause?

arpost — Wed, 11 Oct 2023 17:47:23 GMT

That did the trick! Thanks, Anonymous.

Re: Any ideas on how to pass a string to a stored procedure for an IN clause?

Anonymous — Wed, 11 Oct 2023 17:48:50 GMT

Hi arpost ,
Glad that your issue got resolved. Please continue using Fabric Community for any help regarding your queries.