SQL Declare Variable equivalent in databricks

Question

Can someone let me know if there is an DECLARE equivalent in Databricks SQL

The SQL Code that I have trying to execute with Databricks SQL is as follows:

DECLARE
    @EnrichedViewDatabase sysname, 
    @EnrichedViewSchema sysname, 
    @EnrichedColumnSuffix varchar(50),  
    @LanguageCode varchar(10), 
    @BaseTableSuffix varchar(50),
    @PreviewOnly bit, --Indicate whether to preview the SQL Script (without creating the views) = 1 ; Create views = 0;
    @CurrentDatabase sysname,
    @CurrentDatabaseSchema sysname

SET @EnrichedViewDatabase = 'mydatabasenr1'
SET @EnrichedViewSchema = 'dbo'
SET @EnrichedColumnSuffix = 'code'
SET @LanguageCode = 1033
SET @BaseTableSuffix = ''
SET @PreviewOnly = 0 
SET @CurrentDatabase = 'mydatabasenr2'
SET @CurrentDatabaseSchema = 'dbo'

DECLARE @ColumnMetadata nvarchar(MAX), @ColumnMetadataSQL nvarchar(MAX)

The above SQL gives me the following error:

mismatched input 'DECLARE'

== SQL ==
DECLARE
^^^
    @EnrichedViewDatabase sysname, 
    @EnrichedViewSchema sysname, 
    @EnrichedColumnSuffix varchar(50),  
    @LanguageCode varchar(10), 
    @BaseTableSuffix varchar(50),
    @PreviewOnly bit, --Indicate whether to preview the SQL Script (without creating the views) = 1

Any thoughts?

Answer 1

looks like we can use set only with clusters not sql warehose.

Answer 2

DECLARE is not supported in Databricks SQL. The equivalent code to the one which you are trying to achieve in the above case would be to use SET directly.

%sql

SET EnrichedViewDatabase = 'mydatabasenr1';
SET EnrichedViewSchema = 'dbo';
SET EnrichedColumnSuffix = 'code';
SET LanguageCode = 1033;
SET BaseTableSuffix = '';
SET PreviewOnly = 0 ;
SET CurrentDatabase = 'mydatabasenr2';
SET CurrentDatabaseSchema = 'dbo';

• SET returns a key-value pair as result. To access the value using the respective key (assigned in the above procedure), you can do it in the following way:

%sql

select ${hiveconf:CurrentDatabase} as x,${hiveconf:EnrichedViewDatabase} as y,${hiveconf:LanguageCode} as z