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
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
@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.