How to Use Sql variable within desktop variable in Execute Sql Query action

[RahulRami]

Hi ,

I am trying to retrieve a data from datatable type variable for specific index and try to insert data into table, but some how i am not able to do it.

SQL Code :
==============================
Declare @Ccount int
Set @Ccount = 0
while (@Ccount < %NewVar%)
begin
Set @Ccount = @Ccount + 1
Insert into "TTable" Select %ExcelData[@Ccount]%
end
================================

here 
1. @Ccount is SQL variable 

2. %ExcelData%  power Automation Desktop Datatable variable 

i am trying to retrieve data from specific index from datatable variable %ExcelData[@Ccount]% 
but it not working throw Syntax error message.

please do a need full help. 

[Sonic]

Hi @RahulRami , did you manage to resolve this?Im asking because i basically have the same issue.

[Sonic]

Hi @RahulRami , did you manage to resolve this?Im asking because i basically have the same issue.

[RahulRami]

Hi @Sonic ,
Sorry to say but, I can't find any solution regarding this. 

[kostasc]

Hey @RahulRami,

The issue is that Power Automate Desktop's engine can't evaluate the index (@Ccount) inside the ExcelData datatable. 

Could you please try this approach instead?

The execute SQL statement action should look like this:

[Anonymous]

Hi! 

i know this is already old question ,i just want to answer about SQL query in Power Automate Desktop

I hope when someone stumble same problem  and accessed this post can see it.

the problem for variable inside query is that variable are written like this %yourvariable%.

% is the problem here, you need to add double quotation to escape it like "%yourvariable%"

please see more details over here

https://docs.microsoft.com/en-us/power-automate/desktop-flows/how-to/troubleshoot-sql-queries

have a nice day

[CParsons09]

I tried your suggestion but am getting Sql statement: Syntax error when using the following sql. Any ideas on how to fix? I've tried replacing single with double and vice versa. 

SELECT XPPR.CHECK_NO, 
  TO_CHAR(XPPR.VALUE, '99999999D99') CHECK_AMT, 
  XPPR.SELECTED
FROM XXCA_PR_PAYMENTS_REC XPPR
WHERE 1=1
  AND XPPR.EFFECTIVE_DATE <= NVL('', '31-DEC-9999')
               AND XPPR.PROCESS_NAME = 'Check'
  AND XPPR.CHECK_NO IN ("%CurrentRow[Tran Detail Chk No]%")
ORDER BY XPPR.CHECK_NO, XPPR.EFFECTIVE_DATE, XPPR.EMPLOYEE_NAME

[VJR]

Below query worked for me where InputProcessType is a variable in PAD and ProcessType is an SQL column containing text.

Note the enclosed single quote in red.

SELECT *   FROM MytblName where ProcessType = '%InputProcessType%'

If still not working add the entire sql query in a variable and print it in a displaybox to see what's going wrong with the syntax.

[ApoxiomeN]

Here is your code:

SELECT XPPR.CHECK_NO, 
  TO_CHAR(XPPR.VALUE, '99999999D99') CHECK_AMT, 
  XPPR.SELECTED
FROM XXCA_PR_PAYMENTS_REC XPPR
WHERE 1=1
  AND XPPR.EFFECTIVE_DATE <= NVL('', '31-DEC-9999')
               AND XPPR.PROCESS_NAME = 'Check'
  AND XPPR.CHECK_NO IN ('%CurrentRow['Tran Detail Chk No']%')
ORDER BY XPPR.CHECK_NO, XPPR.EFFECTIVE_DATE, XPPR.EMPLOYEE_NAME

The bold part was the issue. If you're naming the column in your row, the name needs to be put in quotes.
%CurrentRow["Column Name"]%

[CParsons09]

Thank you. I will give that a try and let you know!

[Wakeley]

Thanks. 

I had the same issue, but quotes solved it.
Fx: 

VALUES ('%CustomFormData['TI Product Name']%','%CustomFormData['TI Product Description']%)