How to use parameters in Lookup activity Query

[Mr_Coffee]

Hey All,

I'm new to fabric and running into a syntax issue. 

I have created a lookup activity that queries a on-prem database.

I want the query to return a file name. I'm just trying to learn and test how to do metadata checks to eventually buildout a pipeline that transfers data from files into on-prem db. 

SELECT file_name

FROM dbo.table
WHERE file_name = 'myFirstFile';

I have created a parameter called file_name. I'm manually entering in the following query to be executed in the query lookup activity.

SELECT *
FROM dbo.table
WHERE file_name = @{pipeline().parameters.file_name}

When I click evaluate expression and enter a value for file_name parameter, fabric returns the query with the parameter inserted, but does not execute the query.

Literally, returns the following:

Preview content
SELECT * FROM dbo.table WHERE file_name = myfirstfile

Any suggestions as to what I'm doing wrong?
Thanks in advance!

[Thomaslleblanc · Accepted solution]

Convert the SQL into a stred procedure and when using the Lookup, select stored procedure then you can import the parameters into the lookup activity and place the parameter in the expression builder.

[v-tejrama]

Hi @Mr_Coffee ,

Thanks for reaching out to the Microsoft fabric community forum.

The issue arises because pipeline parameters in Fabric Lookup activities are not automatically recognized as SQL string literals. When you write WHERE file_name = @{pipeline().parameters.file_name}, Fabric inserts the parameter value directly into the query without quotes.

For example, if your parameter is a string such as myFirstFile, the query becomes WHERE file_name = myFirstFile, which SQL interprets as a column or identifier, resulting in an error. To resolve this, enclose the parameter in single quotes within your query: WHERE file_name = '@{pipeline().parameters.file_name}'. This adjustment ensures that SQL processes the value as a string, allowing the query to execute as intended. Please note that the UI preview only shows parameter substitution; the actual query is executed during pipeline runtime. With this change, your Lookup activity should function correctly, enabling you to proceed with your metadata driven pipeline.

Best Regards,
Tejaswi.
Community Support