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
CREATEORALTERPROCEDURE AddPrivacyDepartmentASEXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');
Snowflake Scripting
OUT -> SqlServer_01.sql
CREATEORREPLACEPROCEDURE AddPrivacyDepartment ()RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTEASCALLERAS$$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
IN -> SqlServer_02.sql
EXECUTE AddPrivacyDepartment;SELECT DepartmentID, Name, GroupName FROM HumanResources.Department;
DepartmentID|Name |GroupName |ModifiedDate |
------------+--------------------------+------------------------------------+-----------------------+
1|Engineering |Research and Development |2008-04-30 00:00:00.000|
2|Tool Design |Research and Development |2008-04-30 00:00:00.000|
3|Sales |Sales and Marketing |2008-04-30 00:00:00.000|
4|Marketing |Sales and Marketing |2008-04-30 00:00:00.000|
5|Purchasing |Inventory Management |2008-04-30 00:00:00.000|
6|Research and Development |Research and Development |2008-04-30 00:00:00.000|
7|Production |Manufacturing |2008-04-30 00:00:00.000|
8|Production Control |Manufacturing |2008-04-30 00:00:00.000|
9|Human Resources |Executive General and Administration|2008-04-30 00:00:00.000|
10|Finance |Executive General and Administration|2008-04-30 00:00:00.000|
11|Information Services |Executive General and Administration|2008-04-30 00:00:00.000|
12|Document Control |Quality Assurance |2008-04-30 00:00:00.000|
13|Quality Assurance |Quality Assurance |2008-04-30 00:00:00.000|
14|Facilities and Maintenance|Executive General and Administration|2008-04-30 00:00:00.000|
15|Shipping and Receiving |Inventory Management |2008-04-30 00:00:00.000|
16|Executive |Executive General and Administration|2008-04-30 00:00:00.000|
17|Privacy |Executive General and Administration|2021-11-17 12:42:54.640|
DEPARTMENTID|NAME |GROUPNAME |MODIFIEDDATE |
------------+--------------------------+------------------------------------+-----------------------+
1|Engineering |Research and Development |2021-11-17 10:29:36.963|
2|Tool Design |Research and Development |2021-11-17 10:29:37.463|
3|Sales |Sales and Marketing |2021-11-17 10:29:38.192|
4|Marketing |Sales and Marketing |2021-11-17 10:29:38.733|
5|Purchasing |Inventory Management |2021-11-17 10:29:39.298|
6|Research and Development |Research and Development |2021-11-17 10:31:53.770|
7|Production |Manufacturing |2021-11-17 10:31:55.082|
8|Production Control |Manufacturing |2021-11-17 10:31:56.638|
9|Human Resources |Executive General and Administration|2021-11-17 10:31:57.507|
10|Finance |Executive General and Administration|2021-11-17 10:31:58.473|
11|Information Services |Executive General and Administration|2021-11-17 10:34:35.200|
12|Document Control |Quality Assurance |2021-11-17 10:34:35.741|
13|Quality Assurance |Quality Assurance |2021-11-17 10:34:36.277|
14|Facilities and Maintenance|Executive General and Administration|2021-11-17 10:34:36.832|
15|Shipping and Receiving |Inventory Management |2021-11-17 10:34:37.373|
16|Executive |Executive General and Administration|2021-11-17 10:34:37.918|
17|Privacy |Executive General and Administration|2021-11-17 10:46:43.345|
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.