EXECUTE

Translation reference to convert SQL Server Execute statement to Snowflake

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

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

Snowflake Scripting

CREATE OR REPLACE PROCEDURE AddPrivacyDepartment ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
--** MSC-WARNING - MSCCP0004 - 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

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

Snowflake Scripting

CALL Add_Privacy_Department();
SELECT DEPARTMENTID, NAME, GROUPNAME FROM HUMANRESOURCES.DEPARTMENT;

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

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;

Snowflake Scripting

CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID FLOAT, NEWPRICE NUMBER(38, 4))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    DYNSQLSTATEMENT VARCHAR(300);
  BEGIN
     
    DYNSQLSTATEMENT := 'UPDATE Production.ProductListPriceHistory
   SET
      ListPrice = ' || CAST(:NEWPRICE AS VARCHAR(10)) /*** MSC-WARNING - MSCEWI1046 - 'CAST' FUNCTION MAPPED TO 'CAST', FUNCTIONAL EQUIVALENCE VERIFICATION PENDING ***/ || '
   WHERE
      ProductID = ' || CAST(:PRODUCTID AS VARCHAR(10)) /*** MSC-WARNING - MSCEWI1046 - 'CAST' FUNCTION MAPPED TO 'CAST', FUNCTIONAL EQUIVALENCE VERIFICATION PENDING ***/ || '
      AND EndDate IS NULL;';
    --** MSC-WARNING - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
    EXECUTE IMMEDIATE :DYNSQLSTATEMENT;
  END;
$$;
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO

CALL SetNewPrice(707, 34.99);
SELECT LISTPRICE FROM PRODUCTION.PRODUCTLISTPRICEHISTORY WHERE PRODUCTID = 707 AND ENDDATE IS NULL;

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

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;

Snowflake Scripting

The use of a return status is not currently supported in Snowflake Scripting.

Unsupported Optional arguments

  • @return_status

  • ;number

  • @module_name_var

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

Related EWIs

  1. MSCCP0004: The statement below has usages of dynamic SQL.

PreviousDECLARENextIF

Last updated