Synapse pipeline lookup activity - Select statement with where condition which compares string with '-'

Question

In synapse pipeline I am using look up activity which reads data from Azure SQL Database. SELECT statement has WHERE condition which compares PipelineRunId.

PipelineRunId Example: a3de6326-8af4-43e9-a90e-d2e123f7e6fe

Below is the SELECT statement in the lookup activity:

SELECT COUNT(*) AS cnt FROM [dbo].[test] WHERE   
(Id= @{pipeline().parameters.Id} AND RunId=@{pipeline().parameters.ParentPipelineRunId})  

It throws below error.

{
"errorCode": "BadRequest",
"message": "Operation on target CheckObjectStatus failed: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'af4'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'af4'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'af4'.,},],'",
"failureType": "UserError",
"target": "UpdateExeutionStatus",
"details": ""
}

If I remove RunId condition from WHERE clause SELECT statement in look up activity executes successfully.

Answer 1

Hi @Anonymous ,

I think you are just missing '(Quotation Marks) around the IDs. Please try this:

SELECT COUNT(*) AS cnt FROM [dbo].[test] WHERE
(Id= '@{pipeline().parameters.Id}' AND RunId='@{pipeline().parameters.ParentPipelineRunId}')

Thanks! :)