SnowConvert AI – SQL Server – Azure Synapse – Allgemeine Sprachelemente¶

CREATE TABLE [TestTable](
    [Col1] AS (CONVERT ([REAL], ExpressionValue))
);

CREATE OR REPLACE TABLE TestTable (
    Col1 REAL AS (CAST(ExpressionValue AS REAL)) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

SELECT
            UNIVERSAL_NAME
FROM
            SAMPLE_ATLAS
            AS SATT
            OUTER APPLY
                        /*** MSC-ERROR - MSCCP0001 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/ (SELECT TOP 1
                                                UNIVERSAL_NAME,
                                                INTERNATIONAL_NAME,
                                                CODE_IDENTIFIER
                                    FROM
                                                SAMPLE_GLOBE AS SG
                                    WHERE
                                                SG.GLOBE_KEY = SATT.MbrPersGenKey
                                    ORDER BY GLOBE_KEY
                        );

SELECT
    SATT.UNIVERSAL_NAME
FROM
SAMPLE_ATLAS AS SATT
INNER JOIN LATERAL (
    SELECT
        TOP 1 UNIVERSAL_NAME,
        INTERNATIONAL_NAME,
        CODE_IDENTIFIER
    FROM
        SAMPLE_GLOBE AS SG
    WHERE
        SG.GLOBE_KEY = SATT.MbrPersGenKey
    ORDER BY
        GLOBE_KEY
);

SELECT
	familyName,
	c.givenName AS childGivenName,
	c.firstName AS childFirstName,
	p.givenName AS petName
FROM
	Families f
	LEFT OUTER JOIN
		OPENJSON(f.doc) /*** MSC-WARNING - MSCEWI4030 - Equivalence from CROSS APPLY to LEFT OUTER JOIN must be checked. ***/;
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 7 OF THE SOURCE CODE. **
--		WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
--		CROSS APPLY OPENJSON(children)
--		WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
--			OUTER APPLY OPENJSON (pets)
--			WITH (givenName nvarchar(100))  as p

[MYDATABASE]
[[[MYDATABASE]]

"MYDATABASE"
"[[MYDATABASE]"

-- Additional Params: -d MYCUSTOMDB
USE [MY DATABASE]

CREATE TABLE [TableName1].[TableName2](
	[ColumnName1] varchar NULL
);

-- Additional Params: -d MYCUSTOMDB
USE DATABASE MYCUSTOMDB;

CREATE OR REPLACE TABLE MYCUSTOMDB.TableName1.TableName2 (
	ColumnName1 VARCHAR NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

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

 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);

 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);

 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;

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();

 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;

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();

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;

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();

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;

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();

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;

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();

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;

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();

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;

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();

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;

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();

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;

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();

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.

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;

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();

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.

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;

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();

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.

DECLARE @varname1 nvarchar(50) = 'previous_name'
DECLARE @varname2 nvarchar(50) = 'newer_name'
EXEC sp_rename @varname1, @varname2

DECLARE
VARNAME1 VARCHAR(50) := 'previous_name';
VARNAME2 VARCHAR(50) := 'newer_name';
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || :VARNAME1 || ' RENAME TO ' || :VARNAME2;
END;

EXEC sp_rename 'sample_BACKUP_2.column_old', 'column_new', 'COLUMN'
EXEC sp_rename 'database1.sample_BACKUP_3.column_old', 'column_new', 'COLUMN'

EXECUTE IMMEDIATE 'ALTER TABLE sample_BACKUP_2 RENAME COLUMN column_old TO column_new';

EXECUTE IMMEDIATE 'ALTER TABLE database1.sample_BACKUP_3 RENAME COLUMN column_old TO column_new';

DECLARE @oldColumnName nvarchar(50) = 'previous_name'
DECLARE @newColumnName nvarchar(50) = 'newer_name'
DECLARE @tableName nvarchar(50) = 'TABLE'
EXEC sp_rename @objname = @tableName + '.' + @oldColumnName, @newname = @newColumnName, @objtype = 'COLUMN';

DECLARE
OLDCOLUMNNAME VARCHAR(50) := 'previous_name';
NEWCOLUMNNAME VARCHAR(50) := 'newer_name';
TABLENAME VARCHAR(50) := 'TABLE';
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0075 - TRANSLATION FOR BUILT-IN PROCEDURE 'SP_RENAME' IS NOT CURRENTLY SUPPORTED. ***/!!!
EXEC sp_rename OBJNAME = :TABLENAME || '.' || :OLDCOLUMNNAME, NEWNAME = :NEWCOLUMNNAME, OBJTYPE = 'COLUMN';
END;