Hello. Following an older question SQL Declare Variable equivalent in databricks, we managed to find through the following article Converting Stored Procedures to Databricks | by Ryan Chynoweth | Dec, 2022 | Medium, a way to declaring more complicated parameters:

SET var.total_qty = (

SELECT sum(qty)

FROM my_schema.my_staging_source_table

WHERE YEAR(ModifiedDate) = ${var.year_variable} and CostCenter = $CostCenter;

);

However the above is not executed until there is an action - that means it is passed as a nested query in the basic sql stement that follows ( ${var.total_qty} ). Is there any other way to have the result set in a parameter and not be executed for each row in the basic sql? I am interested only on sql options and not python.

Thank you in advance!