EXECUTE

Translation reference to convert SQL Server Execute statement to Snowflake

Some parts in the output code are omitted for clarity reasons.

Description

Transact-SQL EXECUTE statement allows the execution of a command string or character string within a Transact-SQL batch, a scalar-valued user-defined function, or a stored procedure. For more information regarding SQL Server EXECUTE, check here.

-- Execute a character string  
{ EXEC | EXECUTE }   
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )  
    [ AS { LOGIN | USER } = ' name ' ]  
[;]  

-- Execute a stored procedure or function  
[ { EXEC | EXECUTE } ]  
    {   
      [ @return_status = ]  
      { module_name [ ;number ] | @module_name_var }   
        [ [ @parameter = ] { value   
                           | @variable [ OUTPUT ]   
                           | [ DEFAULT ]   
                           }  
        ]  
      [ ,...n ]  
      [ WITH <execute_option> [ ,...n ] ]  
    }  
[;]  

Sample Source Patterns

Execution of character string

EXECUTE can be used to perform SQL operations passed directly as literals. In the following example it is used within a stored procedure that will insert a new privacy department into the AdventureWorks2019 database.

SQL Server

IN -> SqlServer_01.sql

CREATE OR ALTER PROCEDURE AddPrivacyDepartment
AS 
EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');

Snowflake Scripting

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE AddPrivacyDepartment ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO HumanResources.Department VALUES ('Privacy', 'Executive General and Administration', default);';
END;
$$;

Execution of stored procedure

EXECUTE can also be used to call an existing stored procedure. The following example will call the AddPrivacyDepartment procedure that was created above. It will then run a SELECT to verify that the new department was successfully included.

SQL Server

Code

IN -> SqlServer_02.sql

EXECUTE AddPrivacyDepartment;
SELECT DepartmentID, Name, GroupName FROM HumanResources.Department; 

Result

Snowflake Scripting

Code

OUT -> SqlServer_02.sql

CALL AddPrivacyDepartment();

SELECT
DepartmentID,
Name,
GroupName
FROM
HumanResources.Department;

Result

Execution of local variable and use of parameters

A common use case for the EXECUTE statement is when dynamic SQL statements are needed. In this cases instead of executing a string literal, the statement could be constructed dynamically and assigned to a local variable, which will then be executed. A set of arguments can be sent to the called stored procedure to construct the dynamic SQL command.

In the following example a simple SetNewPrice stored procedure is constructed, which uses the EXECUTE statement to set a new product price based on the arguments sent by the caller. Lastly a SELECT is performed to confirm the new product price.

SQL Server

Code

IN -> SqlServer_03.sql

CREATE OR ALTER PROCEDURE SetNewPrice @ProductID INT, @NewPrice MONEY
AS
  DECLARE @dynSqlStatement AS VARCHAR(300);
  SET @dynSqlStatement = 'UPDATE Production.ProductListPriceHistory SET ListPrice = ' + CAST(@NewPrice AS VARCHAR(10)) + ' WHERE ProductID = ' + CAST(@ProductID AS VARCHAR(10)) + ' AND EndDate IS NULL';
  EXECUTE (@dynSqlStatement);
GO

EXECUTE Set_New_Price @ProductID = 707, @NewPrice = 34.99;
SELECT ListPrice FROM Production.ProductListPriceHistory WHERE ProductID = 707 AND EndDate IS NULL;

Result

Snowflake Scripting

Code

OUT -> SqlServer_03.sql

CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID INT, NEWPRICE NUMBER(38, 4))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    DYNSQLSTATEMENT VARCHAR(300);
  BEGIN
     
    DYNSQLSTATEMENT := 'UPDATE Production.ProductListPriceHistory
   SET
      ListPrice = ' || CAST(:NEWPRICE AS VARCHAR(10)) || '
   WHERE
      ProductID = ' || CAST(:PRODUCTID AS VARCHAR(10)) || '
      AND EndDate IS NULL;';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :DYNSQLSTATEMENT;
  END;
$$;

CALL Set_New_Price(707, 34.99);

SELECT
  ListPrice
FROM
  Production.ProductListPriceHistory
WHERE
  ProductID = 707 AND EndDate IS NULL;

Result

Known Issues

Using return codes

SQL Server EXECUTE syntax contains the @return_status optional argument, which allows creating a scalar variable to store the return status of a scalar-valued user defined function.

It can also be used in stored procedures although the returning status will be limited to integer data type.

To represent this functionality, we could slightly modify the above example and create a user defined function to calculate the new product price as an average of the historical prices. Instead of passing it to the stored procedure, we could now call the CalculateAveragePrice function to obtain the new price, and store it in the return variable to construct the dynamic SQL.

SQL Server

Execute

IN -> SqlServer_04.sql

CREATE OR ALTER FUNCTION CalculateAveragePrice(@pid INT)
RETURNS MONEY
AS
BEGIN
  DECLARE @average AS MONEY;
  SELECT @average = AVG(LISTPRICE) FROM Production.ProductListPriceHistory WHERE ProductID = @pid;
  RETURN @average;
END;
GO

CREATE OR ALTER PROCEDURE SetNewPrice @ProductID INT
AS
  DECLARE @averageHistoricalPrice MONEY;
  EXECUTE @averageHistoricalPrice = [dbo].Calculate_Average_Price @pid=@ProductID;
  UPDATE Production.ProductListPriceHistory SET ListPrice = @averageHistoricalPrice WHERE ProductID =  @ProductID AND EndDate IS NULL;
GO

EXECUTE Set_New_Price @ProductID = 707;
SELECT ListPrice FROM Production.ProductListPriceHistory WHERE ProductID = 707 AND EndDate IS NULL;

Result

Snowflake Scripting

Code

OUT -> SqlServer_04.sql

CREATE OR REPLACE FUNCTION CalculateAveragePrice (PID INT)
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
  WITH CTE1 AS
  (
    SELECT
      AVG(LISTPRICE) AS AVERAGE FROM
      Production.ProductListPriceHistory
    WHERE
      ProductID = PID
  )
  SELECT
    AVERAGE
  FROM
    CTE1
$$;

CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID INT)
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    AVERAGEHISTORICALPRICE NUMBER(38, 4);
    ProcedureResultSet RESULTSET;
  BEGIN
     
    CALL dbo.Calculate_Average_Price(:PRODUCTID);
    UPDATE Production.ProductListPriceHistory
      SET
        ListPrice = :AVERAGEHISTORICALPRICE
      WHERE
        ProductID = :PRODUCTID
        AND EndDate IS NULL;
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL Set_New_Price(707);

SELECT
  ListPrice
FROM
  Production.ProductListPriceHistory
WHERE
  ProductID = 707 AND EndDate IS NULL;

Unsupported Optional arguments

• @return_status

• ;number

• @module_name_var

• WITH RECOMPILE, WITH RESULT SETS NONE, WITH <result set definition>

Related EWIs

1. SSC-EWI-0030: The statement below has usages of dynamic SQL.