- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-11-2022 06:50 AM
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.
- Labels:
-
Databricks SQL
-
DECLARE
-
SQL
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-03-2022 10:54 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-11-2022 04:52 PM
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-12-2022 01:26 AM
Hi,
Ok, thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-12-2022 04:49 AM
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-12-2022 05:31 AM
Thank you for your answer.
How can I use it like this please :
CREATE DATABASE CONCAT({{ database_name }}, 'BackOIffice')
Because it doesn't work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-12-2022 05:36 AM
@Salah K. that won't work because these variables are evaluated outside of SQL.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-12-2022 05:52 AM
Ok.
So in Databricks SQL when can't create Stored Procedure for example ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-12-2022 06:06 AM
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');
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-12-2022 06:20 AM
Ok, thank you very for your time.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-19-2022 02:04 AM
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-14-2022 12:47 AM
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-31-2022 11:00 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-03-2022 10:54 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-22-2023 06:31 PM
This should be the accepted answer!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-03-2022 03:05 PM
Awesome addition, thanks @Ryan Chynoweth !

