EXECUTE
Translation reference to convert SQL Server Execute statement to Snowflake
Last updated
Translation reference to convert SQL Server Execute statement to Snowflake
Last updated
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 ] ]
}
[;] 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.
CREATE OR ALTER PROCEDURE AddPrivacyDepartment
AS
EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');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;
$$;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.
EXECUTE AddPrivacyDepartment;
SELECT DepartmentID, Name, GroupName FROM HumanResources.Department; CALL AddPrivacyDepartment();
SELECT
DepartmentID,
Name,
GroupName
FROM
HumanResources.Department;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.
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;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;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.
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;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;@return_status
;number
@module_name_var
WITH RECOMPILE, WITH RESULT SETS NONE, WITH <result set definition>
SSC-EWI-0030: The statement below has usages of dynamic SQL.