SET a parameter in BEGIN END statement

[oneill]

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

[Vinay_M_R · Accepted solution]

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;

[oneill]

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.