I am new to Databricks SQL and want to create a variable which can hold calculations either from static values or from select queries similar to SQL Server. Is there a way to do so?

[Sudeshna]

I was trying to create a variable and i got the following error -

command -

SET a = 5;

Error -

Error running query

Configuration a is not available.

[User16764241763]

Hello @Sudeshna Bhakat​ 

Thank you for posting on Databricks Community Forum

Could you please use Dashboard filters

https://docs.databricks.com/sql/user/queries/query-filters.html

Query

SELECT l_linenumber AS linenumber, COUNT(*) AS cnt FROM lineitem WHERE l_linenumber = {{var}}

GROUP BY l_linenumber

Query editor looks like below

 

When you add this query to the dashboard, the var looks like below

Dashboard

 

Please let us know if this works for you.

I have filed a feature request internally to support this.

[Anonymous]

Here is an example of the syntax:

set a.variablename = value

Then to use it you need to reference it with ${a.variablename}. See the screenshot

[BilalAslamDbrx · Accepted solution]

@Sudeshna Bhakat​ what @Joseph Kambourakis​ described works on clusters but is restricted on Databricks SQL endpoints i.e. only a limited number of SET commands are allowed. I suggest you explore the curly-braces (e.g. {{ my_variable }}) in Databricks SQL.

[Sudeshna]

Using curly braces means it will create parameters. but i don't need a parameter. There is no way other than creating a parameter?

[BilalAslamDbrx]

No, not yet. For now, you can only use SET commands to set a limited number of configuration options.

[Sudeshna]

Got your point. Thank you very much for the help

[Kaniz]

Hi @Sudeshna Bhakat​ , It Looks like @Bilal Aslam​ ​ 's answer helped you get the correct answer. Would you like to mark it as the best answer as it would help the entire community in the hereafter? Thanks for such a great question.