topic Re: SET a parameter in BEGIN END statement in Data Engineering

SET a parameter in BEGIN END statement

oneill — Sun, 22 Jun 2025 17:37:20 GMT

Hello,

How to set a parameter in a begin end statement. for exemple the following query fails :

begin

SET ansi_mode = true;

end;

with Cannot resolve variable `ANSI_MODE` on search path `SYSTEM`.`SESSION`. SQLSTATE: 42883

Re: SET a parameter in BEGIN END statement

Vinay_M_R — Mon, 23 Jun 2025 05:42:51 GMT

Hello @oneill

There is currently no supported workaround to dynamically change system/session parameters such as ansi_mode within a BEGIN ... END block in Databricks SQL procedures or scripts. Can you set these parameters before executing any procedural logic.

For example:

SET ANSI_MODE = true;
BEGIN
-- procedural code here
END;

Re: SET a parameter in BEGIN END statement

oneill — Mon, 23 Jun 2025 06:46:42 GMT

Hello, thanks for the reply. Yes, it is possible to execute the statement outside the block, but I wanted to make sure there wasn't a special syntax so that the block doesn't consider the parameter as a variable. We'll wait for an update to define parameters inside a begin...end block. There was SET VAR before SQL scripting possibilities. Maybe a SET PARAM inside a block.