SnowConvert: SQLServer Issues¶

SELECT
    -- Supported
    DATEPART(second, getdate()),
    -- Not supported
    DATEPART(millisecond, getdate()),
    DATEPART(microsecond, getdate());

SELECT
    -- Supported
    DATE_PART(second, CURRENT_TIMESTAMP() :: TIMESTAMP),
    -- Not supported
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0060 - TIME PART 'millisecond' NOT SUPPORTED AS A FUNCTION PARAMETER ***/!!!
    DATEPART(millisecond, CURRENT_TIMESTAMP() :: TIMESTAMP),
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0060 - TIME PART 'microsecond' NOT SUPPORTED AS A FUNCTION PARAMETER ***/!!!
    DATEPART(microsecond, CURRENT_TIMESTAMP() :: TIMESTAMP);

SELECT
CONVERT_TIMEZONE('Pacific/Honolulu', CURRENT_TIMESTAMP() !!!RESOLVE EWI!!! /*** SSC-EWI-TS0070 - CURRENT_TIMESTAMP in At Time Zone statement may have a different behavior in certain cases ***/!!!);

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 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 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 FUNCTION func1 ()
RETURNS VARCHAR
SELECT
   *
FROM
   TABLE1

CREATE OR REPLACE FUNCTION func1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0001 - THE BODY WAS NOT GENERATED FOR FUNCTION 'func1' ***/!!!
AS
$$

$$;

-- 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 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()`);
    }
$$;

SELECT TRY_CONVERT( INT, col1) FROM TABLE1;

SELECT TRY_CAST(COL1 AS FLOAT) FROM TABLE1

SELECT
CAST(col1 AS INT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/!!!RESOLVE EWI!!! /*** SSC-EWI-TS0074 - CAST RESULT MAY BE DIFFERENT FROM TRY_CONVERT FUNCTION DUE TO MISSING DEPENDENCIES ***/!!!
FROM
TABLE1;

SELECT
CAST(COL1 AS FLOAT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/!!!RESOLVE EWI!!! /*** SSC-EWI-TS0074 - CAST RESULT MAY BE DIFFERENT FROM TRY_CAST FUNCTION DUE TO MISSING DEPENDENCIES ***/!!!
FROM
TABLE1;

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

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":1, "minor":0},{"attributes":{"component":"transact"}}'
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
$$;

SELECT
    *
FROM GetDepartmentInfo() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TableValuedFunctionCall' NODE ***/!!!;

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

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 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 Helpers Code
   // END REGION

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

EXEC sp_column_privileges_rowset_rmt 'Caption';

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0075 - TRANSLATION FOR BUILT-IN PROCEDURE 'sp_column_privileges_rowset_rmt' IS NOT CURRENTLY SUPPORTED. ***/!!!
EXEC sp_column_privileges_rowset_rmt 'Caption';

CREATE TABLE table1(
  col1 integer,
  col2 varchar collate Latin1_General_CS,
  col3 date
);

ALTER TABLE table1
ADD col4 integer,
  CONSTRAINT col1_constraint DEFAULT 50 FOR col1,
  CONSTRAINT col1_constraint DEFAULT 30 FOR col1;

CREATE OR REPLACE TABLE table1 (
  col1 INTEGER DEFAULT 50,
  col2 VARCHAR COLLATE 'EN-CS',
  col3 DATE
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

ALTER TABLE table1
ADD col4 INTEGER,
  CONSTRAINT col1_constraint
                             !!!RESOLVE EWI!!! /*** SSC-EWI-TS0055 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION ***/!!!
                             DEFAULT 50 FOR col1,
  CONSTRAINT col1_constraint
                             !!!RESOLVE EWI!!! /*** SSC-EWI-TS0055 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION ***/!!!
                             DEFAULT 30 FOR col1;

ALTER TABLE SampleTable
ALTER COLUMN SampleColumn INT NULL SPARSE;

ALTER TABLE IF EXISTS SampleTable
ALTER COLUMN SampleColumn
                          !!!RESOLVE EWI!!! /*** SSC-EWI-TS0061 - ALTER COLUMN COMMENTED OUT BECAUSE SPARSE COLUMN IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                          INT NULL SPARSE;

CREATE PROCEDURE GoToProcedure
AS
BEGIN
DECLARE @TotalMaarks INT
SET @TotalMaarks = 49
IF @TotalMaarks >= 50
    GOTO Pass
IF @TotalMaarks < 50
    GOTO Fail
Pass:
    SELECT 1;
    SELECT * FROM TABLE1;
    RETURN 1;
Fail:
    SELECT 2;
    SELECT * FROM TABLE2;
    RETURN 2;
END

CREATE OR REPLACE PROCEDURE GoToProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        TOTALMAARKS INT;
    BEGIN

        TOTALMAARKS := 49;
        IF (:TOTALMAARKS >= 50) THEN
            !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GOTO' NODE ***/!!!
            GOTO Pass
        END IF;
        IF (:TOTALMAARKS < 50) THEN
            !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GOTO' NODE ***/!!!
            GOTO Fail
        END IF;
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0045 - LABELED STATEMENT IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        Pass:
        SELECT 1;
    SELECT
            *
        FROM
            TABLE1;
        RETURN 1;

        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0045 - LABELED STATEMENT IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        Fail:
        SELECT 2;
    SELECT
            *
        FROM
            TABLE2;
        RETURN 2;

    END;
$$;

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. CODE '80'. **
--END

!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
;

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





		!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT 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 STATEMENT 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;
$$;

ALTER TABLE
    T_ALTERTABLETEST
ADD
    COLUMN COL10 INTEGER DEFAULT RANDOM(10);

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "T_ALTERTABLETEST", "RANDOM" **
ALTER TABLE IF EXISTS T_ALTERTABLETEST
ADD
    COLUMN COL10 INTEGER
                         !!!RESOLVE EWI!!! /*** SSC-EWI-TS0078 - DEFAULT OPTION NOT ALLOWED IN SNOWFLAKE ***/!!!
                         DEFAULT RANDOM(10);

DBCC CHECKIDENT(@a, RESEED, @b) WITH NO_INFOMSGS

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0079 - DATABASE CONSOLE COMMAND 'CHECKIDENT' IS NOT SUPPORTED. ***/!!!
DBCC CHECKIDENT(@a, RESEED, @b) WITH NO_INFOMSGS;

CREATE OR ALTER FUNCTION [PURCHASING].[FOO](@status INT)
Returns INT
As
Begin
    declare @result as int = 10;
    SELECT @result = quantity FROM TABLE1 WHERE COL1 = @status;
    IF @result = 3
    BEGIN
        IF @result>0 SELECT @result=0  ELSE SELECT @result=1
        SELECT @result = 1
    END
    return @result;
End

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO (STATUS INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        RESULT INT := 10;
    BEGIN

        SELECT
            quantity
        INTO
            :RESULT
        FROM
            TABLE1
        WHERE
            COL1 = :STATUS;
        IF (:RESULT = 3) THEN
            BEGIN
                IF (:RESULT >0) THEN SELECT
                        0
                    INTO
                        :RESULT;
                ELSE
                    SELECT
                        1
                    INTO
                        :RESULT;
                END IF;
        SELECT
                    1
                INTO
                    :RESULT;
            END;
        END IF;
        RETURN :RESULT;
    END;
$$;

IF @result = 0
BEGIN
    SET @result =1
END

CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
( SELECT 1 AS RESULT )

IF @result = 0
BEGIN
    Statement1
    Statement2
    Statement3
END

CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
(
    SELECT TransformedStatement3
    FROM (
        SELECT TransformedStatement2
        FROM (
            SELECT TransformedStatement1
        ) T1
    ) T2
)

IF @result = 0
BEGIN
    SET @var1 = 1
    SET @var2 = 3
    SET @var3 = @var2
END

WITH CTE1 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0) = 0 THEN
        (SELECT 1) AS VAR1)
WITH CTE2 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT 3) AS VAR2)
WITH CTE3 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT T1.VAR2
        FROM ((SELECT 3) AS VAR2) AS T1) AS VAR3)
...

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 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 PROCEDURE MySampleProc
    @Param1 NVARCHAR(50) = NULL,
    @Param2 NVARCHAR(10),
    @Param3 NVARCHAR(10) = NULL,
    @Param4 NVARCHAR(10)
AS
    SELECT 1;

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0076 - DEFAULT PARAMETERS MAY NEED TO BE REORDERED. SNOWFLAKE ONLY SUPPORTS DEFAULT PARAMETERS AT THE END OF THE PARAMETERS DECLARATIONS. ***/!!!
CREATE OR REPLACE PROCEDURE MySampleProc (PARAM1 STRING DEFAULT NULL, PARAM2 STRING, PARAM3 STRING DEFAULT NULL, PARAM4 STRING)
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        ProcedureResultSet RESULTSET;
    BEGIN
        ProcedureResultSet := (
        SELECT 1);
        RETURN TABLE(ProcedureResultSet);
    END;
$$;

ALTER TABLE table_name
ALTER COLUMN column_name
ADD MASKED WITH (FUNCTION = 'default()');

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0057 - MASKING ROLE MUST BE DEFINED PREVIOUSLY BY THE USER ***/!!!
CREATE OR REPLACE MASKING POLICY "default" AS
(val STRING)
RETURNS STRING ->
CASE
WHEN current_role() IN ('YOUR_DEFINED_ROLE_HERE')
THEN val
ELSE 'xxxxx'
END;

ALTER TABLE IF EXISTS table_name MODIFY COLUMN column_name!!!RESOLVE EWI!!! /*** SSC-EWI-TS0056 - A MASKING POLICY WAS CREATED AS SUBSTITUTE FOR MASKED WITH ***/!!!  SET MASKING POLICY "default";

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 SOMEPROC(@product_count INT OUTPUT,  @123 INT OUTPUT)
AS
BEGIN
		SELECT * from AdventureWorks.HumanResources.Department;
        SELECT * from AdventureWorks.HumanResources.Employee;
END

CREATE OR REPLACE PROCEDURE SOMEPROC (PRODUCT_COUNT INT, _123 INT)
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;
		return_arr ARRAY := array_construct();
	BEGIN
		ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
			SELECT
				*
			from
				AdventureWorks.HumanResources.Department;
		return_arr := array_append(return_arr, :ProcedureResultSet1);
		ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
			SELECT
				*
			from
				AdventureWorks.HumanResources.Employee;
		return_arr := array_append(return_arr, :ProcedureResultSet2);
		--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
		RETURN return_arr;
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0072 - RETURN statement will be ignored due to previous RETURN statement ***/!!!
		RETURN OBJECT_CONSTRUCT('PRODUCT_COUNT', :PRODUCT_COUNT, '_123', :_123);
	END;
$$;

SELECT *
FROM
    sys.all_sql_modules
WHERE
    [STATE] = 0; -- state must be ONLINE

SELECT
    *
FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0046 - SYSTEM TABLE sys.all_sql_modules IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
    sys.all_sql_modules
WHERE
    STATE = 0; -- state must be ONLINE

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

ALTER TABLE tableName
ALTER COLUMN columnName
ADD MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)');

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0057 - MASKING ROLE MUST BE DEFINED PREVIOUSLY BY THE USER ***/!!!
CREATE OR REPLACE MASKING POLICY "partial_1_xxxxx_1" AS
(val STRING)
RETURNS STRING ->
CASE
WHEN current_role() IN ('YOUR_DEFINED_ROLE_HERE')
THEN val
ELSE LEFT(val, 1) || 'xxxxx' || RIGHT(val, 1)
END;

ALTER TABLE IF EXISTS tableName MODIFY COLUMN columnName!!!RESOLVE EWI!!! /*** SSC-EWI-TS0056 - A MASKING POLICY WAS CREATED AS SUBSTITUTE FOR MASKED WITH ***/!!!  SET MASKING POLICY "partial_1_xxxxx_1";

SELECT current_timestamp at time zone 'Turks And Caicos Standard Time';

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0063 - TIME ZONE NOT SUPPORTED IN SNOWFLAKE ***/!!!
CURRENT_TIMESTAMP() at time zone 'Turks And Caicos Standard Time'
                                                                 ;

-- 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 Helpers Code section is omitted.

    /*** SSC-EWI-0040 - THE STATEMENT 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_638461213351333410;

CREATE OR REPLACE STAGE STAGE_638461213351333410
FILE_FORMAT = FILE_FORMAT_638461213351333410;

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

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

SET @varErrorMessage = ERROR_MESSAGE()

BEGIN
VARERRORMESSAGE := SQLERRM !!!RESOLVE EWI!!! /*** SSC-EWI-TS0073 - ERROR MESSAGE COULD BE DIFFERENT IN SNOWFLAKE ***/!!!;
END;