0

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!

Ray
  • 1
  • 1

3 Answers3

0

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%)

enter image description here

Elina
  • 23
  • 6
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 11 '22 at 12:34
0

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.

tdy
  • 36,675
  • 19
  • 86
  • 83
Kostas Kotsilis
  • 1
  • 1
-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

Elina
  • 23
  • 6
  • [A code-only answer is not high quality](//meta.stackoverflow.com/questions/392712/explaining-entirely-code-based-answers). While this code may be useful, you can improve it by saying why it works, how it works, when it should be used, and what its limitations are. Please [edit] your answer to include explanation and link to relevant documentation. – Stephen Ostermiller May 08 '22 at 11:22