Problem

You want to declare temporary variables inside a function in Databricks SQL, but can’t seem to do it.

Cause

Databricks SQL does not directly support this functionality.

Solution

You must handle your logic using the function parameters and CTEs (Common Table Expressions) without traditional variable declarations.

Example code

In this code, we are creating a function “my_generic_function” that accepts a parameter and returns a table with a computed result. We use CTEs to simulate variable declarations.

• Function Creation: The function my_generic_function accepts a single integer parameter (param_input) and returns a table with one column (computed_result).
   
• Simulated Variable: Since Databricks SQL doesn't support traditional variable declarations, we simulate a variable using a CTE named simulated_variable.
   
• Final Result Calculation: The second CTE (final_result) uses both the simulated variable and the function parameter to compute the result. In this example, it multiplies them together.

Before running this code in a notebook, replace <your-constant-value> with the value you want as the variable’s value. 

CREATE FUNCTION my_generic_function(param_input INT)
RETURNS TABLE(computed_result FLOAT)
RETURN
  WITH simulated_variable AS (
    SELECT <your-constant-value> AS variable_value
  ),
  final_result AS (
    SELECT variable_value * param_input AS computed_result
    FROM simulated_variable
  )
SELECT * FROM final_result;

The final result uses the function parameter and the simulated variable to compute the result. Here, variable_value is the variable declared inside a function in Databricks SQL.