topic Re: Help with Parameters in Databricks SQL in Data Engineering

Help with Parameters in Databricks SQL

AEW — Wed, 12 Apr 2023 15:04:24 GMT

I am following the documentation on query parameters (https://docs.databricks.com/sql/user/queries/query-parameters.html) and it's not working for me. I can't get a parameter inserted at the text caret and I can't get cmd + p to work. I've changed spacing around the curly braces, I've added and removed single quotations. I've tried iterations of ctrl + shift + p, ctrl + tab + p, ctrl + alt + p, etc to get the parameter dialogue to pop up.

Here's my text as it stands now:

select EOWinventory, Monday_Date
from snapshot.weekly_inventory_status
where Monday_Date = '{{projection_date}}'

Does anyone have best practices?

Re: Help with Parameters in Databricks SQL

DD_Sharma — Fri, 14 Apr 2023 07:04:18 GMT

I think you can try this.

Please try adding a space between the Curly brackets "{ { param_name } } "

Re: Help with Parameters in Databricks SQL

User16756723392 — Fri, 14 Apr 2023 07:04:56 GMT

select EOWinventory
from snapshot.weekly_inventory_status
where Monday_Date = '{{projection_date}}' group by Monday_Date

Can you try this

Re: Help with Parameters in Databricks SQL

NandiniN — Fri, 14 Apr 2023 07:29:59 GMT

Hello @Anne Walter ,

Are you calling this from the sql query editor or a workflow?

Thanks & Regards,

Nandini

Re: Help with Parameters in Databricks SQL

NandiniN — Fri, 14 Apr 2023 07:40:02 GMT

You do have to put a space between the brackets on either side of the parameter and it should work

SELECT * FROM {{ schema_table_name }}

Databricks requires that you wrap date and time values in single quotes (')

SELECT *

FROM usage_logs

WHERE date = '{{ date_param }}'

Re: Help with Parameters in Databricks SQL

NandiniN — Fri, 14 Apr 2023 10:54:06 GMT

Hello @User16756723392331126896 (Customer) ,

I tried this out, I was able to get the text query parameter by default which I changed to date by clicking on the gear button.

Here are a few things -

In mac I typed Control + P (not Command + P)

When I tried your query, it actually still did not give me a date parameter, but it was text, I clicked on the gear button and changed it to date from drop down.

Using double quotes seems to have solved it.

I know the document needs update and I will get that going but I hope this helps.

In case it does, please select it as the answer that resolved your issue and let me know if you still face any issue.

Thanks &. Regards,

Nandini

Re: Help with Parameters in Databricks SQL

labromb — Fri, 14 Apr 2023 11:09:05 GMT

I don't see the need for spaces around the parameter name...

I have just been typing {{param_name}} and it appears automatically in SQL editor

Re: Help with Parameters in Databricks SQL

NandiniN — Fri, 14 Apr 2023 17:13:37 GMT

Correct, that was an older workaround, which is now fixed in the later versions, sorry about that.

@Brian Labrom if my answer helps, it would be great if you could upvote it or mark is at Best answer to close the loop.

Re: Help with Parameters in Databricks SQL

AEW — Tue, 18 Apr 2023 16:02:28 GMT

This was the issue! I wasn't calling it from the sql editor. Thank you!