data factory lookup - dynamic content error

arkiboys 9,686 Reputation points
2021-09-08T09:03:47.177+00:00

Hello,
The error I get is --> incorrect syntax near the keyword is

note that in my parameter EntityName, I should be able to either leave it blank or add a value
do you see the issue please?

select * from edp.LoadTables where SchemaName = '@{pipeline().parameters.pSchemaName}' and SourceSystem = '@{pipeline().parameters.pSourceSystem}' and (@{pipeline().parameters.pEntityName} is null OR EntityName = '@{pipeline().parameters.pEntityName}')

in sql it is like the following and it works fine whether you pass in @EntityName or not

declare @EntityName varchar(50) = 'vw_Department'
select * from edp.LoadTables
where SchemaName = 'eol' and SourceSystem = 'xyz'
and (@EntityName is null OR EntityName = @EntityName)

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,933 questions
{count} votes

Accepted answer
  1. Nandan Hegde 30,246 Reputation points MVP
    2021-09-08T10:43:12.513+00:00

    Hey,
    you can use a combination of equals and coalesce to handle that scenario something like below:

    @equals(coalesce(pipeline().parameters.Test,'0'),'0')
    

    for this scenario : @{pipeline().parameters.pEntityName} is null

    0 comments No comments

0 additional answers

Sort by: Most helpful