SnowConvert: Transact DDLs¶

 CREATE PROCEDURE PROC_WITH_DEFAULT_PARAMS1
@PARAM1 INT = 0, @PARAM2 INT = 0, @PARAM3 INT = 0, @PARAM4 INT = 0
AS
BEGIN
    .
    .
    .
END

 CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1(param1 int default 0, param2 int default 0, param3 int default 0, param4 int default 0)
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    .
    .
    .
$$;

 CALL PROC_WITH_DEFAULT_PARAMS1(param2 => 10, param4 => 15);

 CREATE OR REPLACE PROCEDURE PROC1()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
	  var CURSOR = function (stmt, binds) {
	  var statementObj, result_set, total_rows, isOpen = false, self = this, row_count;
	
	  this.CURRENT = new Object;
	
	  var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
	
	  var fixBind = function (arg) {
	      arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
	      return arg;
	   };
	
	  this.OPEN = function(openParameters) {
		  if (result_set == undefined) {
			try {
				if (openParameters) binds = openParameters;
				if (binds instanceof Function) binds = binds();
				var finalBinds = binds && binds.map(fixBind);
				var finalStmt = stmt instanceof Function ? stmt() : stmt;
				statementObj = snowflake.createStatement({
					sqlText : finalStmt,
					binds : finalBinds
				});
				result_set = statementObj.execute();
				total_rows = statementObj.getRowCount();
				isOpen = true;
				row_count = 0;
			} catch(error) {
				RAISE(error.code,"error",error.message);
			}
			else {
				isOpen = true;
			}
		  }
	          
	      return this;
	  };
	      
	  this.CURSOR_ROWS = function () {
	      return total_rows;
	  };
	
	  this.FETCH_STATUS = function() {
	      if(total_rows > row_count)
	          return 0;
	      else
	          return -1;
	  };
	
	  this.FETCH_NEXT = function() {
		  self.res = [];
	      if (isOpen) {
			  self.res = fetch(total_rows,result_set,statementObj);
			  if (self.res)
				  row_count++;
		  }
		  return self.res && self.res.length > 0;
	  };
	
	  this.INTO = function () {
	      return self.res;
	  };
	
	  this.CLOSE = function () {
	      isOpen = false;
	  };
	
	  this.DEALLOCATE = function() {
	      this.CURRENT = row_count = result_set_table = total_rows = result_set = statementObj = self = undefined;
	  };
  };
  
  var COL1, COL2;
  var sql_stmt = ``;
	
  let c = new CURSOR(`SELECT COL1, COL2 FROM TABLE1;`,() => []);
    
  c.OPEN();
  c.FETCH_NEXT();
  [COL1, COL2] = c.INTO();
  while ( c.FETCH_STATUS()) {
        
        
        sql_stmt = `INSERT INTO TABLE2 (COL1, COL2) VALUES (` + COL1+ `, ` + COL2 + `)`;
		
        snowflake.createStatement({
            sqlText : sql_stmt
         }).execute();
  }

  c.CLOSE();
  c.DEALLOCATE();

  return 'sucess';
$$;

   function insertIntoTemporaryTable(sql) {
    var table = "SnowConvertPivotTemporaryTable";
    return EXEC('CREATE OR REPLACE TEMPORARY TABLE ${table} AS ${sql}');
  }
  
  insertIntoTemporaryTable(`${DBTABLES}`)
  EXEC(`INSERT INTO MYDB.PUBLIC.T_Table SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
  EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)

 CREATE PROCEDURE ProcedureLike @VariableValue VARCHAR(50) AS
BEGIN
	IF @VariableValue like '%c%'
	BEGIN
		Select AValue from ATable;
	END;
END;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE ProcedureLike @VariableValue VARCHAR(50) AS
BEGIN
	IF @VariableValue like '%c%'
	BEGIN
		Select AValue from ATable;
	END;
	IF @VariableValue not like '%c%'
	BEGIN
		Select BValue from BTable;
	END;
  IF @VariableValue like '%c!%%' escape '!'
	BEGIN
		Select CValue from CTable;
	END;
END;

 CREATE OR REPLACE PROCEDURE ProcedureLike (VARIABLEVALUE STRING)
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.

	if (LIKE(VARIABLEVALUE,`%c%`)) {
		{
			EXEC(`		Select
		   AValue
		from
		   ATable`);
		}
	}
	if (!LIKE(VARIABLEVALUE,`%c%`)) {
		{
			EXEC(`		Select
		   BValue
		from
		   BTable`);
		}
	}
	if (LIKE(VARIABLEVALUE,`%c!%%`,`!`)) {
		{
			EXEC(`		Select
		   CValue
		from
		   CTable`);
		}
	}
$$;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE MAX_EMPLOYEE_ID
AS
BEGIN
   DECLARE @VARIABLE INT 
   SET @VARIABLE = (SELECT MAX(EMPLOYEE_ID) FROM EMPLOYEES);
   RETURN @VARIABLE
END;

 CREATE OR REPLACE PROCEDURE MAX_EMPLOYEE_ID ()
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.

   let VARIABLE;
   VARIABLE = SELECT(`   MAX(EMPLOYEE_ID) FROM
   EMPLOYEES`);
   return VARIABLE;
$$;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE [PROCEDURE1] AS

DECLARE @VAR1 int;
DECLARE @VAR2 int;
select @VAR1 = col1 + col2, @VAR2 += col1 from table1;

GO

CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
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.

let VAR1;
let VAR2;
SELECT(`   col1 + col2,
   col1
   from
   table1`,[],(value) => VAR1 = value,(value) => VAR2 += value);
$$;

  var IdentityHelper = (seed,increment) => {
      var sequenceString = "`CREATE OR REPLACE SEQUENCE SnowConvert_Temp_Seq START = ${seed} INCREMENT = ${increment}`";
      return EXEC(sequenceString);

    IdentityHelper(1,1)
   EXEC(`CREATE TABLE PUBLIC.department_table3 AS SELECT IDENTITY_UDF() /*** MSC-WARNING - MSCEWI1046 - 'identity' FUNCTION MAPPED TO 'IDENTITY_UDF', FUNCTIONAL EQUIVALENCE VERIFICATION PENDING ***/ as Primary_Rank
from PUBLIC.department_table`);

 // Input code
DECLARE @VAR1 INT = FooSelfAssign(1);
DECLARE @VAR4 INT = FooSelfAssign(FooSelfAssign(FooSelfAssign(FooSelfAssign(4))));

     DECLARE @product_list VARCHAR(MAX) = ' ';
    DECLARE @Variable1 AS VARCHAR(100), @Variable2 AS VARCHAR(100);

 -- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
DECLARE @VariableNameTable TABLE   
 ( 
 [Col1] INT NOT NULL,
 [Col2] INT NOT NULL 
 );
INSERT INTO @VariableNameTable Values(111,222);
Select * from @VariableNameTable;
END

Exec PROC1;

 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.

 {
  EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_VariableNameTable
(
   Col1 INT NOT NULL,
   Col2 INT NOT NULL
)`);
  EXEC(`INSERT INTO T_VariableNameTable Values(111,222)`);
  EXEC(`Select
   *
from
   T_VariableNameTable`);
 }
 EXEC(`CALL PROC1()`);
$$;

 Select * from PUBLIC.T_VariableNameTable;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
	SET @product_list2 = '';
    SET @product_list = '';
    SET @var1 += '';
    SET @var2 &= '';
    SET @var3 ^= '';
    SET @var4 |= '';
    SET @var5 /= '';
    SET @var6 %= '';
    SET @var7 *= '';
    SET @var8 -= '';
    SET @ProviderStatement = 'SELECT * FROM TABLE1
WHERE COL1 = '+@PARAM1+ ' AND COL2 = ' + @LOCALVAR1;
    SET @NotSupported = functionValue(a,b,c);
END
    

 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.
	
	PRODUCT_LIST2 = ``;
	PRODUCT_LIST = ``;
	VAR1 += ``;
	VAR2 &= ``;
	VAR3 ^= ``;
	VAR4 |= ``;
	VAR5 /= ``;
	VAR6 %= ``;
	VAR7 *= ``;
	VAR8 -= ``;
	PROVIDERSTATEMENT = `SELECT
   *
FROM
   TABLE1
WHERE
   COL1 = ${PARAM1}
   AND COL2 = ${LOCALVAR1};`;
	NOTSUPPORTED = SELECT(`   !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'functionValue' NODE ***/!!!
   functionValue(a, b, c)`);
$$;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;

SET NOCOUNT ON
;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;

SET NOCOUNT OFF
;
END

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.

/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET NOCOUNT ON*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET NOCOUNT OFF*/
;
$$;

 IF Conditional_Expression
   -- SQL Statement
ELSE IF Conditiona_Expression2
   -- SQL Statement
ELSE
   -- SQL Statement

 if (Conditional_Expression) {
    // SQL Statement
} else if (Conditional_Expression2) {
    // SQL Statement
} else{
    // SQL Statement
}

 WHILE ( Conditional_Expression )
BEGIN
   -- SQL STATEMENTS
END;

 -- Execute simple statement
Exec('Select 1');

-- Execute statement using Dynamic Sql
Exec('Select ' + @par1 + ' from [db].[t1]');

-- Execute Procedure with parameter
EXEC db.sp2 'Create proc [db].[p3] AS', @par1, 1

 -- Execute simple statement
EXEC(`Select 1`);

-- Execute statement using Dynamic Sql
EXEC(`Select ${PAR1} from MYDB.db.t1`);

-- Execute Procedure with parameter
EXEC(`CALL db.sp2(/*** SSC-EWI-0038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED ***/
'Select * from MYDB.db.t1', ?, 1, Default)`,[PAR1]);

 -- Case 1
THROW

-- Case 2
THROW 123, 'The error message', 1

-- Case 3
THROW @var1, @var2, @var3

-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcSample
AS
BEGIN
IF @@ROWCOUNT > 0
  Continue;
ELSE
  BREAK;
END

CREATE OR REPLACE PROCEDURE ProcSample ()
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.

  if (ROW_COUNT > 0) {
    continue;
  } else {
    break;
  }
$$;

     SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable
    

     DROP TABLE SnowConvertPivotTemporaryTable
    

 INSERT INTO #Table1
EXEC ('SELECT
Table1.ID
FROM Population');

INSERT INTO #Table1
EXEC (@DBTables);

   insertIntoTemporaryTable(`SELECT Table1.ID FROM MYDB.PUBLIC.Population)
  EXEC(`INSERT INTO MYDB.PUBLIC.T_Table1 SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
  EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)
  
  insertIntoTemporaryTable(`${DBTABLES}`)
  EXEC(`INSERT INTO MYDB.PUBLIC.T_Table1 SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
  EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)

 -- Input code
BEGIN TRAN @transaction_name;

 -- Input code
COMMIT TRAN @transaction_name;

 -- Input code
ROLLBACK TRAN @transaction_name;

 -- Input code
1) WAITFOR DELAY '02:00';
2) WAITFOR TIME '13:30';
3) WAITFOR (RECEIVE TOP (1)
   @dh = conversation_handle,
   @mt = message_type_name,
   @body = message_body
   FROM [eqe]), TIMEOUT 5000;

 // Output code
1) EXEC(`SYSTEM$WAIT(120)`,[]);
2) /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
   /*WAITFOR TIME '13:30'*/
   ;
3) /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
   /*WAITFOR (RECEIVE TOP (1)
      @dh = conversation_handle,
      @mt = message_type_name,
      @body = message_body
      FROM [eqe]), TIMEOUT 5000*/
   ;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE [procCursorHelper] AS

DECLARE vendor_cursor CURSOR FOR   
    SELECT VendorID, Name  
    FROM Purchasing.Vendor  
    WHERE PreferredVendorStatus = 1  
    ORDER BY VendorID;
GO

 DECLARE myCursor1 CURSOR FOR SELECT COL1 FROM TABLE1

 OPEN myCursor1
OPEN GLOBAL myCursor2

 DECLARE @VALUE1 INT
FETCH NEXT FROM myCursor1 into @VALUE1

 CLOSE myCursor1
CLOSE GLOBAL myCursor2

 DEALLOCATE myCursor1
DEALLOCATE GLOBAL myCursor2

 @@FETCH_STATUS

 @@CURSOR_ROWS

 SET @var1 = 1 + 1;
SET @var1 = 1 - 1;
SET @var1 = 1 / 1;
SET @var1 = 1 * 1;
SET @var1 = 1 OR 1;
SET @var1 = 1 AND 1;

 VAR1 = 1 + 1;
VAR1 = 1 - 1;
VAR1 = 1 / 1;
VAR1 = 1 * 1;
VAR1 = 1 || 1;
VAR1 = 1 && 1;

 @var1 > 0
@var1 = 0
@var1 < 0
@var1 <> 0

 VAR1 > 0
VAR1 = 0
VAR1 < 0
VAR1 != 0

 -- Additional Params: -t JavaScript
CREATE PROCEDURE [procWithLabels] 
AS
SUCCESS_EXIT:
	SET @ErrorStatus = 0
	RETURN @ErrorStatus

ERROR_EXIT:
	RETURN @ErrorStatus