Power Automate Desktop - Pass User Input as Variable into SQL Query

Question

What is the correct way to take user input in Power Automate Desktop and pass that input as a parameter/variable in an SQL query.

For example, if user enters a value of 3, how do I run a SQL query that looks like -

select * from table where value > 3

Power Automate Desktop stores the user input in a variable and I've tried numerous variations of:

select * from table where value > %UserInput%
select * from table where value > "%UserInput%"
select * from table where value > %[UserInput]%

And I've tried many more variations but I am not getting any results.

Any feedback would be greatly appreciated!

Answer 1

I've got the solution:

"%voucher%"

I put prenthesis for varchar values to be added to SQL base and replace function for adjusting for decimal values

Answer 2

Do you have the sample of the input data? I'll create new question, where I have problem in converting number from one software to PowerAutomate variable for adding it to decimal(18,2) sql table column because of the comma as delimiter, as I suppose. I've tried to use Python script for replacing char in the string, but Python sees this variable as tuple! This worked for me - replacing in sql query replace("%totalGrossAmount%",",","."):

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount, taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, files, createdDate, updatedDate, version) values ("%voucher%", "%organizationId%", "%type%", "%voucherStatus%", "%voucherNumber%", "%voucherDate%", "%shippingDate%", "%dueDate%", replace("%totalGrossAmount%",",","."), replace("%totalTaxAmount%",",","."), "%taxType%", "%useCollectiveContact%", "%contactId%", "%remark%", replace("%amount%",",","."), replace("%taxAmount%",",","."), replace("%taxRatePercent%",",","."), "%categoryId%", "%files%", "%createdDate%", "%updatedDate%", %version%)

Answer 3

If the DB column is VARCHAR:

select * from table where value > '%UserInput%'

If the DB column is INT:

select * from table where value > %UserInput%

You also have to make sure the data in PAD is in line with the data type of the column you are mentioning in the where condition.