SP_EXECUTESQL

Translation specification for the system procedure SP_EXECUTESQL.

Description

The SP_EXECUTESQL system stored procedure is used to execute a Transact-SQL statement or batch that can be reused many times, or one that is built dynamically. The statement or batch can contain embedded parameters.

This functionality can be emulated in Snowflake through the EXECUTE IMMEDIATE statement and with a user-defined function (UDF) for embedded parameters.

For more information about the user-defined function (UDF) used for this translation, check TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(STRING, STRING, ARRAY, ARRAY).

Syntax

Transact

sp_executesql [ @stmt = ] N'statement'
[
    [ , [ @params = ] N'@parameter_name data_type [ { OUT | OUTPUT } ] [ , ...n ]' ]
    [ , [ @param1 = ] 'value1' [ , ...n ] ]
]

Sample Source Patterns

All patterns will transform SP_EXECUTESQL into Snowflake's EXECUTE IMMEDIATE statement and only modify the SQL string to be executed when using embedded parameters.

SSC-EWI-0030 (Usage of Dynamic SQL) will be added for all patterns. Even though the translation for SP_EXECUTESQL is equivalent to Snowflake, in this context, this EWI indicates that the SQL string might require manual fixes for the translation to execute as intended.

Setup Data

Transact

IN -> SqlServer_01.sql

CREATE TABLE PERSONS(
  NAME VARCHAR(25),
  ID INT,
  AGE INT
);

-- DATA
INSERT INTO PERSONS VALUES ('John Smith', 1, 24);
INSERT INTO PERSONS VALUES ('John Doe', 2, 21);
INSERT INTO PERSONS VALUES ('Mary Keller', 3, 32);
INSERT INTO PERSONS VALUES ('Mundane Man', 4, 18);

Snowflake

OUT -> SqlServer_01.sql

CREATE OR REPLACE TABLE PERSONS (
  NAME VARCHAR(25),
  ID INT,
  AGE INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
;

-- DATA
INSERT INTO PERSONS VALUES ('John Smith', 1, 24);
INSERT INTO PERSONS VALUES ('John Doe', 2, 21);
INSERT INTO PERSONS VALUES ('Mary Keller', 3, 32);
INSERT INTO PERSONS VALUES ('Mundane Man', 4, 18);

Without embedded parameters

When no embedded parameters are being used, the SP_EXECUTESQL is transformed into an EXECUTE IMMEDIATE statement and use the SQL string without modifications.

Transact

Code

IN -> SqlServer_02.sql

CREATE PROCEDURE SIMPLE_SINGLE_QUERY
AS
BEGIN
    DECLARE @SQLString NVARCHAR(500);
    SET @SQLString = N'SELECT * FROM PERSONS';
    EXECUTE sp_executesql @SQLString;
END

GO

EXEC SIMPLE_SINGLE_QUERY;

Results

Name	ID	AGE
John Smith	1	24
John Doe	2	21
Mary Keller	3	32
Mundane Man	4	18

Snowflake

Code

OUT -> SqlServer_02.sql

CREATE OR REPLACE PROCEDURE SIMPLE_SINGLE_QUERY ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    SQLSTRING VARCHAR(500);
    ProcedureResultSet RESULTSET;
  BEGIN
     
    SQLSTRING := 'SELECT
   *
FROM
   PERSONS;';
    ProcedureResultSet := (
      !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
      EXECUTE IMMEDIATE :SQLSTRING
    );
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL SIMPLE_SINGLE_QUERY();

Results

Name	ID	AGE
John Smith	1	24
John Doe	2	21
Mary Keller	3	32
Mundane Man	4	18

With embedded parameters for data binding

For embedded parameters for data binding, the SP_EXECUTESQL is transformed into an EXECUTE IMMEDIATE statement, and the SQL string is modified through the TRANSFORM_SP_EXECUTE_SQL_STRING_UDF.

The result of the EXECUTE IMMEDIATE is assigned to the ProcedureResultSet variable and later returned as TABLE(ProcedureResultSet).

Transact

Code

IN -> SqlServer_03.sql

CREATE PROCEDURE QUERY_WITH_DATA_BINDING_PARAMS
AS
BEGIN
    DECLARE @IntVariable INT;
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParmDefinition NVARCHAR(500);

    SET @IntVariable = 21;
    SET @SQLString = N'SELECT * FROM PERSONS WHERE AGE = @age';
    SET @ParmDefinition = N'@age INT';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @age = @IntVariable;
END

GO

EXEC QUERY_WITH_DATA_BINDING_PARAMS;

Results

Name	ID	AGE
John Doe	2	21

Snowflake

Code

OUT -> SqlServer_03.sql

CREATE OR REPLACE PROCEDURE QUERY_WITH_DATA_BINDING_PARAMS ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    INTVARIABLE INT;
    SQLSTRING VARCHAR(500);
    PARMDEFINITION VARCHAR(500);
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
     
    INTVARIABLE := 21;
    SQLSTRING := 'SELECT
   *
FROM
   PERSONS
WHERE
   AGE = @age;';
    PARMDEFINITION := '@age INT';
    ProcedureResultSet := (
      !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
      EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(:SQLSTRING, :PARMDEFINITION, ARRAY_CONSTRUCT('AGE'), ARRAY_CONSTRUCT(:INTVARIABLE))
    );
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL QUERY_WITH_DATA_BINDING_PARAMS();

Results

Name	ID	AGE
John Doe	2	21

With embedded OUTPUT parameters

For embedded OUTPUT parameters, the SP_EXECUTESQL is transformed into an EXECUTE IMMEDIATE statement, and the SQL string is modified through the TRANSFORM_SP_EXECUTE_SQL_STRING_UDF.

Additionally, a SELECT $1, ..., $n INTO :outputParam1, ..., :outputParamN FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) is added to the result of each column to the corresponding OUTPUT parameter.

SSC-FDM-TS0028 is added to the SELECT INTO statement. It is essential for the parameters in the INTO clause to appear in the same order as they were assigned in the original SQL String.

Otherwise, manual changes are required to meet this requirement.

Transact

Code

IN -> SqlServer_04.sql

CREATE PROCEDURE QUERY_WITH_OUTPUT_PARAMS
AS
BEGIN
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParamDefinition NVARCHAR(500);
    DECLARE @MaxAge INT;

    SET @SQLString = N'SELECT @MaxAgeOUT = max(AGE) FROM PERSONS';
    SET @ParamDefinition = N'@MaxAgeOUT INT OUTPUT';
    EXECUTE sp_executesql @SQLString, @ParamDefinition, @MaxAgeOUT = @MaxAge OUTPUT;

    SELECT @MaxAge;
END

GO

EXEC QUERY_WITH_OUTPUT_PARAMS;

Results

<anonymous>
32

Snowflake

Code

OUT -> SqlServer_04.sql

CREATE OR REPLACE PROCEDURE QUERY_WITH_OUTPUT_PARAMS ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/27/2024",  "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        SQLSTRING VARCHAR(500);
        PARAMDEFINITION VARCHAR(500);
        MAXAGE INT;
        ProcedureResultSet RESULTSET;
    BEGIN
         
         
         
        SQLSTRING := 'SELECT
   MAX(AGE) FROM
   PERSONS;';
        PARAMDEFINITION := '@MaxAgeOUT INT OUTPUT';
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(:SQLSTRING, :PARAMDEFINITION, ARRAY_CONSTRUCT('MAXAGEOUT'), ARRAY_CONSTRUCT(:MAXAGE));
        --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
        SELECT
            $1
        INTO
            :MAXAGE
        FROM
            TABLE(RESULT_SCAN(LAST_QUERY_ID()));
        ProcedureResultSet := (
        SELECT
            :MAXAGE);
        RETURN TABLE(ProcedureResultSet);
    END;
$$;

CALL QUERY_WITH_OUTPUT_PARAMS();

Results

:MAXAGE::NUMBER(38,0)
32

With both embedded OUTPUT parameters and data binding

The translation is the same as for only OUTPUT parameters.

Transact

Code

IN -> SqlServer_05.sql

CREATE PROCEDURE QUERY_WITH_BOTH_PARAMS
AS
BEGIN
    DECLARE @AgeVariable INT;
    DECLARE @IdVariable INT;
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParmDefinition NVARCHAR(500);
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    SET @AgeVariable = 30;
    SET @IdVariable = 100;
    SET @SQLString = N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE AGE < @age AND ID < @id;';
    SET @ParmDefinition = N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @age = @AgeVariable, @id = @IdVariable, @MaxAgeOUT = @MaxAge OUTPUT, @MaxIdOUT = @MaxId OUTPUT;

    SELECT @MaxAge, @MaxId;
END

GO

EXEC QUERY_WITH_BOTH_PARAMS;

Results

<anonymous>	<anonymous>
24	4

Snowflake

Code

OUT -> SqlServer_05.sql

CREATE OR REPLACE PROCEDURE QUERY_WITH_BOTH_PARAMS ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    AGEVARIABLE INT;
    IDVARIABLE INT;
    SQLSTRING VARCHAR(500);
    PARMDEFINITION VARCHAR(500);
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
     
     
     
     
    AGEVARIABLE := 30;
    IDVARIABLE := 100;
    SQLSTRING := 'SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   AGE < @age AND ID < @id;';
    PARMDEFINITION := '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(:SQLSTRING, :PARMDEFINITION, ARRAY_CONSTRUCT('AGE', 'ID', 'MAXAGEOUT', 'MAXIDOUT'), ARRAY_CONSTRUCT(:AGEVARIABLE, :IDVARIABLE, :MAXAGE, :MAXID));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXAGE,
      :MAXID
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL QUERY_WITH_BOTH_PARAMS();

Results

:MAXAGE::NUMBER(38,0)	:MAXID::NUMBER(38,0)
24	4

Parameters not in order of definition

This pattern follows the same rules as the previous patterns. TRANSFORM_SP_EXECUTE_SQL_STRING_UDF replaces the parameter values in the correct order.

Transact

Code

IN -> SqlServer_06.sql

CREATE PROCEDURE QUERY_PARAMS_NOT_IN_ORDER_OF_DEF
AS
BEGIN
    DECLARE @AgeVariable INT;
    DECLARE @IdVariable INT;
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParmDefinition NVARCHAR(500);
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    SET @AgeVariable = 30;
    SET @IdVariable = 100;
    SET @SQLString = N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE AGE < @age AND ID < @id;';
    SET @ParmDefinition = N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @id = @IdVariable, @MaxAgeOUT = @MaxAge OUTPUT, @age = @AgeVariable, @MaxIdOUT = @MaxId OUTPUT;

    SELECT @MaxAge, @MaxId;
END

GO

EXEC QUERY_PARAMS_NOT_IN_ORDER_OF_DEF;

CREATE PROCEDURE QUERY_PARAMS_NOT_IN_ORDER_OF_DEF_2
AS
BEGIN
    DECLARE @AgeVariable INT;
    DECLARE @IdVariable INT;
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParmDefinition NVARCHAR(500);
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    SET @AgeVariable = 30;
    SET @IdVariable = 100;
    SET @SQLString = N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE AGE < @age AND ID < @id;';
    SET @ParmDefinition = N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @AgeVariable, @MaxAgeOUT = @MaxAge OUTPUT, @id = @IdVariable, @MaxIdOUT = @MaxId OUTPUT;

    SELECT @MaxAge, @MaxId;
END

GO

EXEC QUERY_PARAMS_NOT_IN_ORDER_OF_DEF_2;

Results

<anonymous>	<anonymous>
24	4

<anonymous>	<anonymous>
24	4

Snowflake

Code

OUT -> SqlServer_06.sql

CREATE OR REPLACE PROCEDURE QUERY_PARAMS_NOT_IN_ORDER_OF_DEF ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    AGEVARIABLE INT;
    IDVARIABLE INT;
    SQLSTRING VARCHAR(500);
    PARMDEFINITION VARCHAR(500);
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
     
     
     
     
    AGEVARIABLE := 30;
    IDVARIABLE := 100;
    SQLSTRING := 'SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   AGE < @age AND ID < @id;';
    PARMDEFINITION := '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(:SQLSTRING, :PARMDEFINITION, ARRAY_CONSTRUCT('ID', 'MAXAGEOUT', 'AGE', 'MAXIDOUT'), ARRAY_CONSTRUCT(:IDVARIABLE, :MAXAGE, :AGEVARIABLE, :MAXID));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXAGE,
      :MAXID
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL QUERY_PARAMS_NOT_IN_ORDER_OF_DEF();

CREATE OR REPLACE PROCEDURE QUERY_PARAMS_NOT_IN_ORDER_OF_DEF_2 ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    AGEVARIABLE INT;
    IDVARIABLE INT;
    SQLSTRING VARCHAR(500);
    PARMDEFINITION VARCHAR(500);
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
     
     
     
     
    AGEVARIABLE := 30;
    IDVARIABLE := 100;
    SQLSTRING := 'SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   AGE < @age AND ID < @id;';
    PARMDEFINITION := '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(:SQLSTRING, :PARMDEFINITION, ARRAY_CONSTRUCT('', 'MAXAGEOUT', 'ID', 'MAXIDOUT'), ARRAY_CONSTRUCT(:AGEVARIABLE, :MAXAGE, :IDVARIABLE, :MAXID));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXAGE,
      :MAXID
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL QUERY_PARAMS_NOT_IN_ORDER_OF_DEF_2();

Results

:MAXAGE::NUMBER(38,0)	:MAXID::NUMBER(38,0)
24	4

:MAXAGE::NUMBER(38,0)	:MAXID::NUMBER(38,0)
24	4

Execute direct values

This translation also handles the cases where the values a directly assigned instead of using variables.

Transact

Code

IN -> SqlServer_07.sql

CREATE PROCEDURE QUERY_WITH_DIRECT_PARAMS_VALUES_ALL
AS
BEGIN
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    EXECUTE sp_executesql
        N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE ID < @id AND AGE < @age;',
        N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT',
        30,
        100,
        @MaxAge OUTPUT,
        @MaxId OUTPUT;

    SELECT @MaxAge, @MaxId;
END

GO

EXEC QUERY_WITH_DIRECT_PARAMS_VALUES_ALL;

Results

<anonymous>	<anonymous>
24	4

Snowflake

Code

OUT -> SqlServer_07.sql

CREATE OR REPLACE PROCEDURE QUERY_WITH_DIRECT_PARAMS_VALUES_ALL ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/07/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF('SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   ID < @id AND AGE < @age;', '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT', ARRAY_CONSTRUCT('', '', '', ''), ARRAY_CONSTRUCT(
    30,
    100, :MAXAGE, :MAXID));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXAGE,
      :MAXID
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL QUERY_WITH_DIRECT_PARAMS_VALUES_ALL();

Results

:MAXAGE::NUMBER(38,0)	:MAXID::NUMBER(38,0)
24	4

SQL string dynamically built

This pattern follows the same rules as the previous patterns. However, assigning the result of the EXECUTE IMMEDIATE statement might not be added if the SQL string is not a simple single query with or without embedded parameters.

Furthermore, the SQL string must start with the literal value 'SELECT' for SnowConvert to correctly identify that a SELECT statement is going to be executed. For more information, check the Known Issues section.

Transact

Code

IN -> SqlServer_08.sql

CREATE PROCEDURE DYNAMIC_WITH_PARAMS
AS
BEGIN
    DECLARE @IntVariable INT;
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParmDefinition NVARCHAR(500);
    DECLARE  @where_clause nvarchar(100);

    SET @where_clause = 'WHERE AGE = @age';
    SET @IntVariable = 21;
    SET @SQLString = N'SELECT * FROM PERSONS ' + @where_clause;
    SET @ParmDefinition = N'@age INT';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @age = @IntVariable;
END

GO

EXEC DYNAMIC_WITH_PARAMS;

Results

Name	ID	AGE
John Doe	2	21

Snowflake

Code

OUT -> SqlServer_08.sql

CREATE OR REPLACE PROCEDURE DYNAMIC_WITH_PARAMS ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    INTVARIABLE INT;
    SQLSTRING VARCHAR(500);
    PARMDEFINITION VARCHAR(500);
    WHERE_CLAUSE VARCHAR(100);
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
     
     
    WHERE_CLAUSE := 'WHERE AGE = @age';
    INTVARIABLE := 21;
    SQLSTRING := 'SELECT
   *
FROM
   PERSONS ' || :WHERE_CLAUSE || ';';
    PARMDEFINITION := '@age INT';
    ProcedureResultSet := (
      !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
      EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(:SQLSTRING, :PARMDEFINITION, ARRAY_CONSTRUCT('AGE'), ARRAY_CONSTRUCT(:INTVARIABLE))
    );
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL DYNAMIC_WITH_PARAMS();

Results

Name	ID	AGE
John Doe	2	21

Returning multiple result sets

Snowflake Scripting procedures only allow one result set to be returned per procedure.

To replicate Teradata behavior, when two or more result sets are to be returned, they are stored in temporary tables. The Snowflake Scripting procedure will return an array containing the names of the temporary tables. For more information, check SSC-FDM-0020.

Transact

Code

IN -> SqlServer_09.sql

CREATE PROCEDURE WITH_MULTIPLE_RETURNS
AS
BEGIN
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParmDefinition NVARCHAR(500);

    SET @SQLString = N'SELECT * FROM PERSONS WHERE AGE = @age';
    SET @ParmDefinition = N'@age INT';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @age = 21;

    SET @SQLString = N'INSERT INTO PERSONS VALUES (''INSERT FIRST'', 1200, 230);';
    EXECUTE sp_executesql @SQLString;

    SET @SQLString = N'SELECT * FROM PERSONS';
    EXECUTE sp_executesql @SQLString;
END

GO

EXECUTE WITH_MULTIPLE_RETURNS;

Results

Name	ID	AGE
John Doe	2	21

Name	ID	AGE
John Smith	1	24
John Doe	2	21
Mary Keller	3	32
Mundane Man	4	18
INSERT FIRST	1200	230

Snowflake

Code

OUT -> SqlServer_09.sql

CREATE OR REPLACE PROCEDURE WITH_MULTIPLE_RETURNS ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/07/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    SQLSTRING VARCHAR(500);
    PARMDEFINITION VARCHAR(500);
    ProcedureResultSet1 VARCHAR;
    ProcedureResultSet2 VARCHAR;
    return_arr ARRAY := array_construct();
  BEGIN
     
     
    SQLSTRING := 'SELECT
   *
FROM
   PERSONS
WHERE
   AGE = @age;';
    PARMDEFINITION := '@age INT';
    ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(:SQLSTRING, :PARMDEFINITION, ARRAY_CONSTRUCT('AGE'), ARRAY_CONSTRUCT(21));
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
      SELECT
        *
      FROM
        TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    return_arr := array_append(return_arr, :ProcedureResultSet1);
    SQLSTRING := 'INSERT INTO PERSONS VALUES ('INSERT FIRST', 1200, 230);';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :SQLSTRING;
    SQLSTRING := 'SELECT
   *
FROM
   PERSONS;';
    ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :SQLSTRING;
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
      SELECT
        *
      FROM
        TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    return_arr := array_append(return_arr, :ProcedureResultSet2);
    --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
    RETURN return_arr;
  END;
$$;

CALL WITH_MULTIPLE_RETURNS();

Results

WITH_MULTIPLE_RETURNS
[ "RESULTSET_88C35D7A_1E5B_455D_97A4_247806E583A5", "RESULTSET_B2345B61_A015_43CB_BA11_6D3E013EF262" ]

Known Issues

1. Invalid code is detected

SP_EXECUTESQL can execute more than one SQL statement inside the SQL string. Snowflake also supports executing multiple SQL statements, but need to be enclosed in a BEGIN ... END block. Furthermore, when executing multiple statements from a BEGIN ... END block, the EXECUTE IMMEDIATE will not return a resultset. The translation for these cases is not yet supported by SnowConvert. For more information, check SSC-EWI-0030.

Thus, when this case is detected, in the translated code, the EXECUTE IMMEDIATE will not be assigned to the ProcedureResultSet.

Transact

Code

IN -> SqlServer_10.sql

CREATE PROCEDURE WITH_INVALID_CODE_DETECTED
AS
BEGIN
    DECLARE @SQLString NVARCHAR(500);
    SET @SQLString = N'INSERT INTO PERSONS VALUES (''INSERT FIRST'', 1200, 230); SELECT * FROM PERSONS;';
    EXECUTE sp_executesql @SQLString;
END

GO

EXEC WITH_INVALID_CODE_DETECTED;

Results

Name	ID	AGE
John Smith	1	24
John Doe	2	21
Mary Keller	3	32
Mundane Man	4	18
INSERT FIRST	1200	230

Snowflake

Code

OUT -> SqlServer_10.sql

CREATE OR REPLACE PROCEDURE WITH_INVALID_CODE_DETECTED ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    SQLSTRING VARCHAR(500);
  BEGIN
     
    SQLSTRING := 'INSERT INTO PERSONS VALUES ('INSERT FIRST', 1200, 230); SELECT
   *
FROM
   PERSONS;';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :SQLSTRING;
  END;
$$;

CALL WITH_INVALID_CODE_DETECTED();

Results

000006 (0A000): Uncaught exception of type 'STATEMENT_ERROR' on line 10 at position 4 : Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

2. Valid or Invalid code is not detected

When the SQL string is built dynamically through concatenations, SnowConvert might not detect what statement is going to be executed. Thus, in the translated code, the EXECUTE IMMEDIATE will not be assigned to the ProcedureResultSet.

Transact

Code

IN -> SqlServer_11.sql

CREATE PROCEDURE WITH_INVALID_CODE_NOT_DETECTED
AS
BEGIN
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @SQLInsert NVARCHAR(500);
    SET @SQLInsert = N'INSERT INTO PERSONS VALUES (''INSERT FIRST'', 1200, 230)';
    SET @SQLString = @SQLInsert + N'SELECT * FROM PERSONS;';
    EXECUTE sp_executesql @SQLString;
END

GO

EXEC WITH_INVALID_CODE_NOT_DETECTED;

Results

Name	ID	AGE
John Smith	1	24
John Doe	2	21
Mary Keller	3	32
Mundane Man	4	18
INSERT FIRST	1200	230

Snowflake

Code

OUT -> SqlServer_11.sql

CREATE OR REPLACE PROCEDURE WITH_INVALID_CODE_NOT_DETECTED ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    SQLSTRING VARCHAR(500);
    SQLINSERT VARCHAR(500);
  BEGIN
     
     
    SQLINSERT := 'INSERT INTO PERSONS VALUES ('INSERT FIRST', 1200, 230);';
    SQLSTRING := :SQLINSERT || 'SELECT * FROM PERSONS;';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :SQLSTRING;
  END;
$$;

CALL WITH_INVALID_CODE_NOT_DETECTED();

Results

000006 (0A000): Uncaught exception of type 'STATEMENT_ERROR' on line 10 at position 4 : Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

3. Invalid code is mistaken as valid

If the SQL string starts with a SELECT statement and is followed by more statements, SnowConvert will detect this as a valid code and try to assign the result of the EXECUTE IMMEDIATE to theProcedureResultSet. This leads to a compilation error. For more information, check SSC-EWI-0030.

Transact

Code

IN -> SqlServer_12.sql

CREATE PROCEDURE WITH_INVALID_CODE_MISTAKEN_AS_VALID
AS
BEGIN
    DECLARE @SQLString NVARCHAR(500);
    SET @SQLString = N'SELECT * FROM PERSONS; SELECT * FROM PERSONS;';
    EXECUTE sp_executesql @SQLString;
END

GO

EXEC WITH_INVALID_CODE_MISTAKEN_AS_VALID;

Results

Name	ID	AGE
John Smith	1	24
John Doe	2	21
Mary Keller	3	32
Mundane Man	4	18

Name	ID	AGE
John Smith	1	24
John Doe	2	21
Mary Keller	3	32
Mundane Man	4	18

Snowflake

Code

OUT -> SqlServer_12.sql

CREATE OR REPLACE PROCEDURE WITH_INVALID_CODE_MISTAKEN_AS_VALID ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/04/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    SQLSTRING VARCHAR(500);
    ProcedureResultSet RESULTSET;
  BEGIN
     
    SQLSTRING := 'SELECT
   *
FROM
   PERSONS; SELECT
   *
FROM
   PERSONS;';
    ProcedureResultSet := (
      !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
      EXECUTE IMMEDIATE :SQLSTRING
    );
    RETURN TABLE(ProcedureResultSet);
  END;
$$;

CALL WITH_INVALID_CODE_MISTAKEN_AS_VALID();

Results

000006 (0A000): Uncaught exception of type 'STATEMENT_ERROR' on line 10 at position 4 : Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

Related EWIs

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

2. SSC-FDM-TS0028: Output parameters must have the same order as they appear in the executed code.

3. SSC-FDM-0020: Multiple result sets are returned in temporary tables.