bug with using parameters in a sql task

[kenmyers-8451]

I am trying to make a sql task that runs using a serverless sql warehouse that takes a variable and uses that in the sql file that it is running in a serverless warehouse, however I am getting errors because databricks keeps formatting it first with quotation marks. For example let's say the file I'm trying to run just has USE CATALOG my_catalog_{{env}} and my task has an env parameter with the value dev. This sql ends up getting formatted as USE CATALOG my_catalog_'dev' which clearly isn't valid sql. This seems like a bug, I don't think databricks should be adding quotes to these values because there are many cases where a user would want to format something without quotes around it. I can provide a workflow sample if this helps.

[kenmyers-8451]

Here is another reason I believe this is bugged. I'm looking at the documentation on how to format sql using parameters and I see almost this exact example for formatting the catalog. It gives two options, you can format with curly braces or with the IDENTIFIER function. I've gotten the IDENTIFIER function to work for us, but the curly brace version is adding extra quotation marks.

[Renu_]

Hi @kenmyers-8451, when you use the curly bracket syntax like {{env}}, it treats the value as a string and automatically adds quotes, that’s why you’re seeing something like my_catalog_'dev'. If you’re trying to use dynamic SQL object names, it’s better to avoid the curly brackets and instead use the colon syntax with IDENTIFIER() to reference the parameter correctly.

[kenmyers-8451]

@Renu_ consider this example

ALTER TABLE some.table SET OWNER TO IDENTIFIER('`' || :owner_name || '`')

You can't use IDENTIFIER here, it throws an error (it is not a supported use case). If you try to do 

ALTER TABLE some.table SET OWNER TO `{{owner_name}}`

this gets formatted to 

 ALTER TABLE some.table SET OWNER TO `'my_owners_name'` 

Which errors because those quotes it adds are invalid here. There is NO way to format this sql with a task parameter.