Buidling SQLqueries in dynamic content ADF

Question

Im trying to use generic values for my SQL query. I have created parameters for a DB, schema and a table that should be passed into the FROM clause, and i ahve tried two approaches as can be seen in the SQL queries. But as is im getting thsi error:

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Could not find stored procedure 'SELECT *

FROM [{$database}].[{$schema}].[{$table}]

WHERE CreationDate > '2023-08-01T11:08:34Z''.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Could not find stored procedure 'SELECT *

FROM [{$database}].[{$schema}].[{$table}]

WHERE CreationDate > '2023-08-01T11:08:34Z''.,Source=.Net SqlClient Data Provider,SqlErrorNumber=2812,Class=16,ErrorCode=-2146232060,State=62,Errors=[{Class=16,Number=2812,State=62,Message=Could not find stored procedure 'SELECT *

FROM [{$database}].[{$schema}].[{$table}]

WHERE CreationDate > '2023-08-01T11:08:34Z''.,},],'

"SELECT * 
FROM pipeline().parameters.database.pipeline().parameters.schema.pipeline().parameters.table
WHERE CreationDate > '2023-08-01T11:08:34Z'"

"SELECT * 
FROM [{$database}].[{$schema}].[{$table}]
WHERE CreationDate > '2023-08-01T11:08:34Z'"

Answer 1

Hi Mathias Ravn Stephansen,

Thank you for posting query in Microsoft Q&A Platform. Glad to know your issue resolved and thank you for sharing your implementation details.

You can also consider using string interpolation syntax as below as well.

Kindly consider marking it as accept answer as you cannot mark your own answer as accepted. Accepted answers help community as well. Thank you.

Answer 2

Okay dont look at the time for when this was posted :)

But the solution is here, and i also parameterized the creation date

@concat('SELECT * 
FROM "', pipeline().parameters.database, '".', pipeline().parameters.schema, '.', pipeline().parameters.table, '
WHERE CreationDate > ''', pipeline().parameters.date, '''')