What's the equivalent of "DECLARE..." in Databricks SQL ?

[wyzer]

Hello everyone,

I'm new in Databricks SQL, and I'm comming from SQL Server.

I would like to know what's the equivalent of :

DECLARE @P_Name varchar(50) = 'BackOffice'

It's for use it like this :

CREATE DATABASE @P_Name

Thanks.

[Anonymous]

Hi there, @Salah K.​! Welcome to the community and thanks for your question. Let's give the community more time to answer your question. Thanks!

[wyzer]

Hi,

Ok, thanks.

[BilalAslamDbrx]

@Salah K.​  Databricks SQL does not support T-SQL style variable declarations. However, Databricks SQL _does_ support variables in a different way.

Try this: "

CREATE DATABASE {{ database_name }}

You will see a "widget" where you can pass in the database name

[wyzer]

Thank you for your answer.

How can I use it like this please :

CREATE DATABASE CONCAT({{ database_name }}, 'BackOIffice')

Because it doesn't work.

[BilalAslamDbrx]

@Salah K.​  that won't work because these variables are evaluated outside of SQL.

[wyzer]

Ok.

So in Databricks SQL when can't create Stored Procedure for example ?

[BilalAslamDbrx]

No, we do support UDFs (our name for sprocs). For example:

CREATE OR REPLACE FUNCTION generate_db_name(name STRING COMMENT 'Database name')
  RETURNS STRING
  COMMENT 'Creates a database name postfixed by BackOffice'
  CONTAINS SQL DETERMINISTIC
  RETURN name || 'BackOffice'

However, this fails as it appears CREATE DATABASE only accepts a string literal.

create database generate_db_name('Testing');

[wyzer]

Ok, thank you very for your time.

[Bilal1]

Use python and build up a sql string which can be executed.

e,g

a = 'backoffice'

sql = f'select * from {a}'

print(sql)

spark.sql(sql)

[wyzer]

Thanks

[sauri]

Hello,

Maybe you are looking that:

 %sql

CREATE WIDGET TEXT P_Name DEFAULT 'BackOffice';

CREATE DATABASE getArgument("P_Name ")

https://docs.databricks.com/notebooks/widgets.html#widgets-in-sql

[Ryan_Chynoweth · Accepted solution]

Please note that SQL Variables are supported in pure SQL. Here is an example notebook. https://github.com/rchynoweth/DemoContent/blob/main/Misc/SQL_Variables.sql

The syntax would be something like the following:

SET var.database_name = my_database ; 
 
SELECT * FROM ${var.database_name}.my_table; 
 

Note that the "var" can be any string i.e. it can be c.var_name instead.

[verargulla]

This should be the accepted answer!

[Anonymous]

Awesome addition, thanks @Ryan Chynoweth​ !