SnowConvert AI - SQL ServerとAzure Synapseの問題¶

 CREATE FUNCTION func1 ()
RETURNS VARCHAR
SELECT
   *
FROM
   TABLE1

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE1" **
CREATE OR REPLACE FUNCTION func1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0001 - THE BODY WAS NOT GENERATED FOR FUNCTION 'func1' ***/!!!
AS
$$

$$;

 SET ANSI_PADDING OFF;

 !!!RESOLVE EWI!!! /*** SSC-EWI-TS0002 - THE ANSI_PADDING OFF IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
SET ANSI_PADDING OFF;

 SET ANSI_WARNINGS OFF;

 !!!RESOLVE EWI!!! /*** SSC-EWI-TS0003 - THE ANSI_WARNINGS OFF IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
SET ANSI_WARNINGS OFF;

 CREATE PROC transactionsTest
AS
BEGIN TRANSACTION 
   SELECT @@TRANCOUNT AS TransactionCount_AfterFirstTransaction 
   INSERT INTO TESTSCHEMA.TESTTABLE(ID) VALUES (1), (2) 
   BEGIN TRANSACTION 
      SELECT @@TRANCOUNT AS TransactionCount_AfterSecondTransaction 
      INSERT INTO TESTSCHEMA.TESTTABLE(ID) VALUES (3), (4) 
   COMMIT;
   SELECT @@TRANCOUNT AS TransactionCount_AfterFirstCommit  
COMMIT;
END;

 CREATE OR REPLACE PROCEDURE transactionsTest ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  ProcedureResultSet1 VARCHAR;
  ProcedureResultSet2 VARCHAR;
  ProcedureResultSet3 VARCHAR;
  return_arr ARRAY := array_construct();
 BEGIN
  !!!RESOLVE EWI!!! /*** SSC-EWI-TS0009 - THE FOLLOWING TRANSACTION MAY CONTAIN NESTED TRANSACTIONS WHICH ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!!
  BEGIN TRANSACTION;
  ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
  CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
   SELECT
    :TRANCOUNT AS TransactionCount_AfterFirstTransaction;
  return_arr := array_append(return_arr, :ProcedureResultSet1);
  INSERT INTO TESTSCHEMA.TESTTABLE (ID) VALUES (1), (2);
  BEGIN TRANSACTION;
  ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
  CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
   SELECT
    :TRANCOUNT AS TransactionCount_AfterSecondTransaction;
  return_arr := array_append(return_arr, :ProcedureResultSet2);
  INSERT INTO TESTSCHEMA.TESTTABLE (ID) VALUES (3), (4);
  COMMIT;
  ProcedureResultSet3 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
  CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet3) AS
   SELECT
    :TRANCOUNT AS TransactionCount_AfterFirstCommit;
  return_arr := array_append(return_arr, :ProcedureResultSet3);
  COMMIT;
  --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
  RETURN return_arr;
 END;
$$;
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '12' COLUMN '1' OF THE SOURCE CODE STARTING AT 'END'. EXPECTED 'BATCH' GRAMMAR. **
--END
   ;

 Create View viewName
as
with commonTableExpressionName (
   columnName
) as
(
   select
      1
)
((select
   1 as col2)
union
(
   select
      1 as col3
));

 !!!RESOLVE EWI!!! /*** SSC-EWI-TS0010 - COMMON TABLE EXPRESSION IN VIEW NOT SUPPORTED IN SNOWFLAKE. ***/!!!
CREATE OR REPLACE VIEW viewName
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - WITH CTE NOT SUPPORTED IN SNOWFLAKE ***/!!!
with commonTableExpressionName (
   columnName
) as
(
   select
      1
)
((select
   1 as col2)
union
(
   select
      1 as col3
));

 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"}}'
;

 CREATE TABLE table1
(
    column1 customType,
);

 CREATE OR REPLACE TABLE table1
(
    column1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-TS0015 - DATA TYPE CUSTOMTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

 SELECT {fn CURRENT_DATE_UDF()};

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "CURRENT_DATE_UDF" **
SELECT
CURRENT_DATE_UDF() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CURRENT_DATE_UDF' NODE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0016 - USER DEFINED FUNCTIONS ARE NOT SUPPORTED IN ODBC SCALAR FUNCTION. ***/!!!;

 CREATE TABLE TABLE1
( 
  [COL1] nvarchar MASKED WITH (FUNCTION = 'default()') NULL,
  [COL2] varchar(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
  [COL3] varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
  [COL4] smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL
);

 CREATE OR REPLACE TABLE TABLE1
(
  COL1 VARCHAR
               !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
               MASKED WITH (FUNCTION = 'default()') NULL,
  COL2 VARCHAR(100)
                    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
  COL3 VARCHAR(100)
                    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'email()') NOT NULL,
  COL4 SMALLINT
                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
                MASKED WITH (FUNCTION = 'random(1, 100)') NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

 BULK INSERT #PCE FROM 'E:\PCE_Look-up_table.txt'  
WITH   
(  
   FIELDTERMINATOR ='\t',
   ROWTERMINATOR ='\n',
   FIRE_TRIGGERS 
);

 CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638461199649565070
FIELD_DELIMITER = '\t'
RECORD_DELIMITER = '\n'
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0023 - 'FIRE_TRIGGERS' BULK OPTION COULD NOT BE TRANSFORMED TO ANY OF THE EXISTING FILE FORMAT OPTIONS ***/!!!
FIRE_TRIGGERS;

CREATE OR REPLACE STAGE STAGE_638461199649565070
FILE_FORMAT = FILE_FORMAT_638461199649565070;

--** SSC-FDM-TS0004 - PUT STATEMENT IS NOT SUPPORTED ON WEB UI. YOU SHOULD EXECUTE THE CODE THROUGH THE SNOWFLAKE CLI **
PUT file://E:\PCE_Look-up_table.txt @STAGE_638461199649565070 AUTO_COMPRESS = FALSE;

COPY INTO T_PCE FROM @STAGE_638461199649565070/PCE_Look-up_table.txt;

 CREATE PROCEDURE BULK_PROC2
AS
BULK INSERT dbo.table1 FROM 'E:\test.txt'  
WITH   
(  
   FIELDTERMINATOR ='\t',
   ROWTERMINATOR ='\n'
); 

GO

 CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638461207064166040
FIELD_DELIMITER = '\t'
RECORD_DELIMITER = '\n';

CREATE OR REPLACE STAGE STAGE_638461207064166040
FILE_FORMAT = FILE_FORMAT_638461207064166040;

--** SSC-FDM-TS0004 - PUT STATEMENT IS NOT SUPPORTED ON WEB UI. YOU SHOULD EXECUTE THE CODE THROUGH THE SNOWFLAKE CLI **
PUT file://E:\test.txt @STAGE_638461207064166040 AUTO_COMPRESS = FALSE;

CREATE OR REPLACE PROCEDURE BULK_PROC2 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert AI Helpers Code
   // END REGION

   EXEC(`COPY INTO dbo.table1 FROM @STAGE_638461207064166040/test.txt`);
$$

 -- Additional Params: -t JavaScript
CREATE procedure proc1()
as
BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0 from table1;  
END TRY  
BEGIN CATCH  
    return ERROR_SEVERITY();  
END CATCH;  
GO  

 CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert AI Helpers Code section is omitted.

    try {
        EXEC(`    -- Generate a divide-by-zero error.  
    SELECT
       TRUNC( 1/0) from
       table1`);
    } catch(error) {
        return SELECT(`   !!!RESOLVE EWI!!! /*** SSC-EWI-TS0025 - CUSTOM UDF 'ERROR_SEVERITY_UDF' INSERTED FOR ERROR_SEVERITY FUNCTION. ***/!!!
   ERROR_SEVERITY_UDF()`);
    }
$$;

 WITH Duplicated AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RN
FROM WithQueryTest
)
DELETE FROM Duplicated
WHERE Duplicated.RN > 1

 !!!RESOLVE EWI!!! /*** SSC-EWI-TS0026 - WITH DELETE QUERY TURNED TO CREATE TABLE ***/!!!
CREATE OR REPLACE TABLE WithQueryTest AS
SELECT
*
FROM
WithQueryTest
QUALIFY
ROW_NUMBER()
OVER (PARTITION BY
ID
ORDER BY ID) = 1;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE  [dbo].[Load_FuelMgtMasterData]
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE
            @SQLString VARCHAR(500)
        ,   @ImportName VARCHAR(200)
        ,   @Today DATE
        ,   @Yesterday DATE
        ,   @SourceAffiliates VARCHAR(200);

        SET @Today = GETDATE();
        SET @Yesterday = DATEADD(DAY, -1, @Today);
        TRUNCATE TABLE dbo.SourceFM_Affiliates;
        SET @ImportName = '\\' + +@@ServerName
            + '\WorkA\merchantportal\affiliates.txt';
        SET @SQLString = 'BULK INSERT ' + @SourceAffiliates + ' FROM '''
            + @ImportName + '''';
        EXEC (@SQLString);
    END;

 CREATE OR REPLACE PROCEDURE dbo.Load_FuelMgtMasterData ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert AI Helpers Code section is omitted.

    /*** SSC-EWI-0040 - THE 'SET' CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/
    /*        SET NOCOUNT ON*/
    ;
    let SQLSTRING;
    let IMPORTNAME;
    let TODAY;
    let YESTERDAY;
    let SOURCEAFFILIATES;
    TODAY = SELECT(`   CURRENT_TIMESTAMP() :: TIMESTAMP`);
    YESTERDAY = SELECT(`   DATEADD(DAY, -1, ?)`,[TODAY]);
    EXEC(`        TRUNCATE TABLE dbo.SourceFM_Affiliates`);
    IMPORTNAME = `\\` + SERVERNAME + `\WorkA\merchantportal\affiliates.txt`;
    SQLSTRING =
        // ** SSC-EWI-TS0032 - THE BULK INSERT WAS PART OF A DYNAMIC SQL, WHICH MAKES SOME OF THE TRANSLATED ELEMENTS INVALID UNLESS EXECUTED OUTSIDE DYNAMIC CODE. **
        `CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638923328992788100;

CREATE OR REPLACE STAGE STAGE_638923328992788100
FILE_FORMAT = FILE_FORMAT_638923328992788100;

PUT file://${IMPORTNAME} @STAGE_638923328992788100 AUTO_COMPRESS = FALSE;

COPY INTO ${SOURCEAFFILIATES}
FROM @STAGE_638923328992788100/${IMPORTNAME}`;
    EXEC(`${SQLSTRING}`);
$$;

 CREATE FUNCTION Sales.ufn_SalesByStore2()
RETURNS TABLE
AS
RETURN
(
  WITH CTE AS (
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
  )
  SELECT tab.* FROM CTE tab
);

GO

SELECT * FROM GetDepartmentInfo();

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "HumanResources.Department" **
CREATE OR REPLACE FUNCTION Sales.ufn_SalesByStore2 ()
RETURNS TABLE(
  DepartmentID STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN DepartmentID WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  Name STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Name WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  GroupName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN GroupName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
AS
$$
  --** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
    WITH CTE AS (
    SELECT
      DepartmentID,
      Name,
      GroupName
    FROM
      HumanResources.Department
    )
    SELECT tab.* FROM
    CTE tab
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "GetDepartmentInfo" **

SELECT
    *
FROM
    TABLE(GetDepartmentInfo());

 CREATE OR ALTER PROCEDURE notInitializedCursorTest
AS
BEGIN
    -- Should be marked with SSC-EWI-TS0035
    DECLARE @MyCursor CURSOR, @MyCursor2 CURSOR;
    -- Should not be marked
    DECLARE cursorVar CURSOR FORWARD_ONLY STATIC READ_ONLY
        FOR
        SELECT someCol
        FROM someTable;
    RETURN 'DONE';
END;

 CREATE OR REPLACE PROCEDURE notInitializedCursorTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        -- Should be marked with SSC-EWI-TS0035
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0035 - CURSOR VARIABLE DECLARED BUT NEVER INITIALIZED, THIS IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        MYCURSOR CURSOR;
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0035 - CURSOR VARIABLE DECLARED BUT NEVER INITIALIZED, THIS IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        MYCURSOR2 CURSOR;
        -- Should not be marked
        cursorVar CURSOR
        FOR
            SELECT
                someCol
            FROM
                someTable;
    BEGIN
         
         
        RETURN 'DONE';
    END;
$$;

 CREATE OR ALTER PROCEDURE globalCursorTest
AS
BEGIN
    -- Should be marked with SSC-EWI-TS0036
    DECLARE MyCursor CURSOR GLOBAL STATIC READ_ONLY
        FOR
        SELECT *
        FROM exampleTable;
    -- Should not be marked
    DECLARE MyCursor2 CURSOR LOCAL STATIC READ_ONLY
        FOR
        SELECT testCol
        FROM myTable;
    RETURN 'DONE';
END;

 CREATE OR REPLACE PROCEDURE globalCursorTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        -- Should be marked with SSC-EWI-TS0036
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0036 - SNOWFLAKE SCRIPTING ONLY SUPPORTS LOCAL CURSORS ***/!!!
        MyCursor CURSOR
        FOR
            SELECT
                *
            FROM
                exampleTable;
        -- Should not be marked
        MyCursor2 CURSOR
        FOR
            SELECT
                testCol
            FROM
                myTable;
    BEGIN
         
         
        RETURN 'DONE';
    END;
$$;

 CREATE OR ALTER PROCEDURE scrollablecursorTest
AS
BEGIN
    -- Should be marked with SSC-EWI-TS0037
    DECLARE CursorVar CURSOR SCROLL STATIC READ_ONLY
	FOR  
	SELECT FirstName
	FROM vEmployee;
    -- Should not be marked
    DECLARE CursorVar2 CURSOR STATIC READ_ONLY
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE CursorVar3 CURSOR FORWARD_ONLY STATIC READ_ONLY
	FOR  
	SELECT FirstName
	FROM vEmployee;
    RETURN 'DONE';
END;

 CREATE OR REPLACE PROCEDURE scrollablecursorTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		-- Should be marked with SSC-EWI-TS0037
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0037 - SNOWFLAKE SCRIPTING CURSORS ARE NON-SCROLLABLE, ONLY FETCH NEXT IS SUPPORTED ***/!!!
		CursorVar CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		-- Should not be marked
		CursorVar2 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		CursorVar3 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
	BEGIN
		 
		 
		 
		RETURN 'DONE';
	END;
$$;

 CREATE OR ALTER PROCEDURE multipleSetExample
AS
BEGIN
    DECLARE @MyCursor CURSOR;
    DECLARE @MyCursor2 CURSOR STATIC READ_ONLY
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE @MyCursor3 CURSOR;
    
    SET @MyCursor = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;
        
    SET @MyCursor3 = CURSOR STATIC READ_ONLY
    FOR
    SELECT *
    FROM someTable;
    
    SET @MyCursor = CURSOR DYNAMIC
        FOR
        SELECT col2
        FROM exampleTable;
        
    SET @MyCursor2 = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;
        
    RETURN 'DONE';
END;

 CREATE OR REPLACE PROCEDURE multipleSetExample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		MYCURSOR CURSOR
		FOR
			SELECT col3
			FROM defaultTable;
		MYCURSOR2 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		MYCURSOR3 CURSOR
		FOR
			SELECT *
			FROM someTable;
	BEGIN
		 
		 
		 
		 
		 
		DECLARE
		MYCURSOR CURSOR
		FOR
			SELECT col3
			FROM defaultTable;
		MYCURSOR2 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		MYCURSOR3 CURSOR
		FOR
			SELECT *
			FROM someTable;
	BEGIN
		 
		 
		 
		 
		 
		!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE 'SET CURSOR' CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0039 - CURSOR VARIABLE MYCURSOR SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING ***/!!!

		SET @MyCursor = CURSOR DYNAMIC
		    FOR
		    SELECT col2
		    FROM exampleTable;
		!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE 'SET CURSOR' CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0039 - CURSOR VARIABLE MYCURSOR2 SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING ***/!!!

    SET @MyCursor2 = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;
		RETURN 'DONE';
	END;
$$;

 CREATE PROCEDURE xml_procedure
    @inUserGroupsXML XML
AS
BEGIN
    SELECT  entities.entity.value('TypeID[1]', 'VARCHAR(100)') AS TypeID
        ,entities.entity.value('Name[1]', 'VARCHAR(100)') AS Name
    INTO  #tmpUserGroups
    FROM  @inUserGroupsXML.nodes('/entities/entity') entities(entity)
END;

 CREATE OR REPLACE PROCEDURE xml_procedure (INUSERGROUPSXML TEXT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CREATE OR REPLACE TEMPORARY TABLE T_tmpUserGroups AS
            SELECT
                XMLGET(entity, '$') :: VARCHAR(100) AS TypeID
                ,
                XMLGET(entity, '$') :: VARCHAR(100) AS Name
            FROM
                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0041 - XML TYPE METHOD nodes IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                T_inUserGroupsXML('/entities/entity') entities (
                    entity
                );
    END;
$$;

 WITH XMLNAMESPACES ('uri' as ns1)
SELECT ProductID as 'ns1:ProductID',
Name      as 'ns1:Name',
Color     as 'ns1:Color'
FROM Production.Product
WHERE ProductID = 316
FOR XML RAW, ELEMENTS XSINIL

 --** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH
     !!!RESOLVE EWI!!! /*** SSC-EWI-TS0043 - WITH XMLNAMESPACES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
 XMLNAMESPACES ('uri' as VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-TS0015 - DATA TYPE NS1 IS NOT SUPPORTED IN SNOWFLAKE ***/!!! NOT NULL)
SELECT
ProductID AS "ns1:ProductID",
Name AS "ns1:Name",
Color AS "ns1:Color"
FROM
Production.Product
WHERE
ProductID = 316
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW, ELEMENTS XSINIL;

 CREATE  TABLE PRODUCT (ProductID INTEGER, Name VarChar(20), Color VarChar(20));
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

WITH XMLNAMESPACES ('uri' as ns1)  
SELECT ProductID as 'ns1:ProductID',  
       Name      as 'ns1:Name',  
       Color     as 'ns1:Color'  
FROM Product  
FOR XML RAW

 CREATE OR REPLACE TABLE PRODUCT (
       ProductID INTEGER,
       Name VARCHAR(20),
       Color VARCHAR(20))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/12/2024" }}'
;

INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **

WITH
     !!!RESOLVE EWI!!! /*** SSC-EWI-TS0043 - WITH XMLNAMESPACES IS NOT SUPPORTED IN SNOWFLAKE ***/!!! XMLNAMESPACES ('uri' as ns1)
SELECT
       ProductID AS "ns1:ProductID",
       Name AS "ns1:Name",
       Color AS "ns1:Color"
FROM
       Product
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW;

 SELECT TOP 1 LastName
FROM AdventureWorks2019.Person.Person
FOR XML AUTO;

 SELECT TOP 1
LastName
FROM
AdventureWorks2019.Person.Person
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML AUTO CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML AUTO;

 CREATE TABLE TEMPTABLE (Ref INT, Des NVARCHAR(100), Qty INT)

INSERT INTO tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2)

GO

-- FOR XML
SELECT *
FROM TempTable
FOR XML AUTO

GO
 
-- FOR XML RAW
SELECT *
FROM TempTable
FOR XML RAW

 -- FOR XML
<TempTable Ref="100001" Des="Normal" Qty="1"/><TempTable Ref="100002" Des="Foobar" Qty="1"/><TempTable Ref="100003" Des="Hello World" Qty="2"/>

-- FOR XML RAW
<row Ref="100001" Des="Normal" Qty="1"/><row Ref="100002" Des="Foobar" Qty="1"/><row Ref="100003" Des="Hello World" Qty="2"/>

 CREATE OR REPLACE TABLE TEMPTABLE (
Ref INT,
Des VARCHAR(100),
Qty INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2);

-- FOR XML
SELECT
*
FROM
TempTable
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML AUTO CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML AUTO;

-- FOR XML RAW
SELECT
*
FROM
TempTable
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW;

 -- FOR XML
<TempTable DES="Normal" QTY="1" REF="100001"  /><TempTable DES="Foobar" QTY="1" REF="100002"  /><TempTable DES="Hello World" QTY="2" REF="100003"  />

-- FOR XML RAW
<row DES="Normal" QTY="1" REF="100001"  /><row DES="Foobar" QTY="1" REF="100002"  /><row DES="Hello World" QTY="2" REF="100003"  />

CREATE PROCEDURE dbo.RetryDatabaseConnection
AS
BEGIN
    DECLARE @Attempts INT = 0
RetryConnection:
    SET @Attempts = @Attempts + 1
    IF @Attempts < 3
        GOTO RetryConnection
    RETURN 0
END

CREATE OR REPLACE PROCEDURE dbo.RetryDatabaseConnection ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    ATTEMPTS INT := 0;
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0045 - LABELED STATEMENT IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
    RetryConnection:
    ATTEMPTS := :ATTEMPTS + 1;
    IF (:ATTEMPTS < 3) THEN
      !!!RESOLVE EWI!!! /*** SSC-EWI-TS0087 - GOTO IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      GOTO RetryConnection
    END IF;
    RETURN 0;
  END;
$$;