Azure Logic App and passing multi-value parameters to a Stored Procedure

Recently I had the need to pass multiple values to a stored procedure in a Logic App setup. Turned out it wasn't straight plug-and-play OOTB but still fairly easy to achieve. I stumbled upon this post by Dave on the telerik forums which uses a table valued function to do the magic.

In short, the limitation is not by Logic Apps in itself but rather how stored procedures work.

Anyways in my little setup I had a Logic App that I wanted to pass multiple parameters to and depending on the output result I had to take action accordingly.

Here is an example setup of a logic app.

Essentially I want to pass some store codes as the input us,au,nz,dk,se,no,uk to the stored procedure which is comma separated in this case. You can choose the delimiter you would like to use - you'll have to adjust the table valued function below with your delimiter.

The stored procedure itself is as follows and the highlight of the stored procedure is in the WHERE clause where I use the IN operator.

CREATE PROCEDURE [dbo].[ExampleImportProcedure]
(
	@StoreCodes nvarchar(1000) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT OrderId, OrderStatus, vStoreCode
	FROM [dbo].[OrderTbl]
	WHERE vStoreCode IN (SELECT * FROM splitstring(@StoreCodes)) AND OrderStatus = 'NewOrder' AND DATEADD(HOUR, 2, CreatedAt) < CURRENT_TIMESTAMP
	RETURN
END

The @StoreCodes are passed in to a table valued function splitstring which splits the store codes into a proper format which in this case returns a rowset which the IN operator accepts as an argument, a subquery.

CREATE FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(8000) )
    RETURNS
        @returnList TABLE ([Param] [nvarchar] (500))
AS
 
BEGIN
 
    DECLARE @name NVARCHAR(255)
    DECLARE @pos INT
 
    WHILE CHARINDEX(',', @stringToSplit) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(',', @stringToSplit) 
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
 
        INSERT INTO @returnList
        SELECT @name
 
        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END
 
    INSERT INTO @returnList
    SELECT @stringToSplit
 
    RETURN
END

That's it - fairly simple and easy to use.

Let me know if there is a better way to achieve the same result.