SnowConvert: Snowflake Scripting¶

 CREATE OR REPLACE PROCEDURE TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DROP TABLE IF EXISTS NEWTABLE;
        CREATE OR REPLACE TABLE NEWTABLE (
            COL1 INT,
            COL2 VARCHAR
        );
            BEGIN TRANSACTION;
            INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
         INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
            COMMIT;
    END;
$$;

 CREATE OR REPLACE PROCEDURE TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DROP TABLE IF EXISTS NEWTABLE;
        CREATE OR REPLACE TABLE NEWTABLE (
            COL1 INT,
            COL2 VARCHAR
        );
            BEGIN TRANSACTION
            !!!RESOLVE EWI!!! /*** SSC-EWI-0101 - COMMENTED OUT TRANSACTION LABEL NAME BECAUSE IS NOT APPLICABLE IN SNOWFLAKE ***/!!!
            LabelA;
            INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
        INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
            COMMIT;
    END;
$$;

 CREATE OR ALTER PROCEDURE SelectCaseDemoProcedure
AS
      SELECT TOP 10
          LOGINID,
          CASE (MARITALSTATUS)
              WHEN 'S' THEN 'SINGLE'
              WHEN 'M' THEN 'MARIED'
              ELSE 'OTHER'
          END AS status
      FROM HUMANRESOURCES.EMPLOYEE;
GO

EXEC SelectCaseDemoProcedure;

 CREATE OR ALTER PROCEDURE SelectCaseDemoProcedure
AS
      SELECT TOP 10
          LOGINID,
          CASE
              WHEN MARITALSTATUS = 'S' THEN 'SINGLE'
              WHEN MARITALSTATUS = 'M' THEN 'MARIED'
              ELSE 'OTHER'
          END AS status
      FROM HUMANRESOURCES.EMPLOYEE;
GO

EXEC SelectCaseDemoProcedure;

 sqlLOGINID              |status|
------------------------+------+
adventure-works\ken0    |SINGLE|
adventure-works\terri0  |SINGLE|
adventure-works\roberto0|MARIED|
adventure-works\rob0    |SINGLE|
adventure-works\gail0   |MARIED|
adventure-works\jossef0 |MARIED|
adventure-works\dylan0  |MARIED|
adventure-works\diane1  |SINGLE|
adventure-works\gigi0   |MARIED|
adventure-works\michael6|MARIED|

CREATE OR REPLACE PROCEDURE SelectCaseDemoProcedure ()
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 TOP 10
                  LOGINID,
                CASE (MARITALSTATUS)
                    WHEN 'S' THEN 'SINGLE'
                    WHEN 'M' THEN 'MARIED'
                    ELSE 'OTHER'
                END AS status
            FROM
                  HUMANRESOURCES.EMPLOYEE);
            RETURN TABLE(ProcedureResultSet);
      END;
$$;

CALL SelectCaseDemoProcedure();

 CREATE OR REPLACE PROCEDURE SelectCaseDemoProcedure ()
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 TOP 10
                  LOGINID,
                CASE
                    WHEN MARITALSTATUS = 'S' THEN 'SINGLE'
                    WHEN MARITALSTATUS = 'M' THEN 'MARIED'
                    ELSE 'OTHER'
                END AS status
            FROM
                  HUMANRESOURCES.EMPLOYEE);
            RETURN TABLE(ProcedureResultSet);
      END;
$$;

CALL SelectCaseDemoProcedure();

 LOGINID                |STATUS|
-----------------------+------+
adventure-worksken0    |SINGLE|
adventure-works erri0  |SINGLE|
adventure-worksoberto0 |MARIED|
adventure-worksob0     |SINGLE|
adventure-worksgail0   |MARIED|
adventure-worksjossef0 |MARIED|
adventure-worksdylan0  |MARIED|
adventure-worksdiane1  |SINGLE|
adventure-worksgigi0   |MARIED|
adventure-worksmichael6|MARIED|

 CREATE OR ALTER PROCEDURE SetCaseDemoProcedure
AS
    DECLARE @value INT;
    DECLARE @result INT;
    SET @value = 5;
    
    SET @result =
        CASE @value
            WHEN 1 THEN @value * 10
            WHEN 3 THEN @value * 20
            WHEN 5 THEN @value * 30
            WHEN 7 THEN @value * 40
            ELSE -1
        END;
    
    RETURN @result
GO

DECLARE @result INT;
EXEC @result = SetCaseDemoProcedure;
PRINT @result;

 CREATE OR ALTER PROCEDURE SetCaseDemoProcedure
AS
    DECLARE @value INT;
    DECLARE @result INT;
    SET @value = 5;
    
    SET @result =
        CASE
            WHEN @value = 1 THEN @value * 10
            WHEN @value = 3 THEN @value * 20
            WHEN @value = 5 THEN @value * 30
            WHEN @value = 7 THEN @value * 40
            ELSE -1
        END;
    
    RETURN @result
GO

DECLARE @result INT;
EXEC @result = SetCaseDemoProcedure;
PRINT @result;

 |result|
|------|
|150   |

 CREATE OR REPLACE PROCEDURE SetCaseDemoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VALUE INT;
        RESULT INT;
    BEGIN
         
         
        VALUE := 5;
        CASE (:VALUE)
            WHEN 1 THEN
                RESULT := :VALUE * 10;
            WHEN 3 THEN
                RESULT := :VALUE * 20;
            WHEN 5 THEN
                RESULT := :VALUE * 30;
            WHEN 7 THEN
                RESULT := :VALUE * 40;
            ELSE
                RESULT := -1;
        END;
        RETURN :RESULT;
    END;
$$;

DECLARE
    RESULT INT;
BEGIN
    CALL SetCaseDemoProcedure();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
    PRINT @result;
END;

CREATE OR REPLACE PROCEDURE SetCaseDemoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VALUE INT;
        RESULT INT;
    BEGIN
         
         
        VALUE := 5;
        CASE
            WHEN :VALUE = 1 THEN
                RESULT := :VALUE * 10;
            WHEN :VALUE = 3 THEN
                RESULT := :VALUE * 20;
            WHEN :VALUE = 5 THEN
                RESULT := :VALUE * 30;
            WHEN :VALUE = 7 THEN
                RESULT := :VALUE * 40;
            ELSE
                RESULT := -1;
        END;
        RETURN :RESULT;
    END;
$$;

DECLARE
    RESULT INT;
BEGIN
    CALL SetCaseDemoProcedure();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
    PRINT @result;
END;

 |result|
|------|
|150   |

 CREATE PROC SampleProcedure AS;

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

 CREATE OR ALTER PROCEDURE Add_Privacy_Department
AS
  EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');

 CREATE OR REPLACE PROCEDURE Add_Privacy_Department ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE 'INSERT INTO HumanResources.Department VALUES ('Privacy', 'Executive General and Administration', default);';
  END;
$$;

 ALTER PROCEDURE procedureName
AS
SELECT 1 AS ThisDB;

 CREATE OR REPLACE PROCEDURE procedureName ()
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 AS ThisDB);
RETURN TABLE(ProcedureResultSet);
END;
$$;

 CREATE OR ALTER PROCEDURE SetNewPrice @ProductID INT, @NewPrice MONEY
AS
  BEGIN
    DECLARE @dynSqlStatement AS VARCHAR(300);
    SET @dynSqlStatement = 'UPDATE Production.ProductListPriceHistory SET ListPrice = ' + CAST(@NewPrice AS VARCHAR(10)) + ' WHERE ProductID = ' + CAST(@ProductID AS VARCHAR(10)) + ' AND EndDate IS NULL';
    EXECUTE (@dynSqlStatement);
  END;

 CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID INT, NEWPRICE NUMBER(38, 4))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    DYNSQLSTATEMENT VARCHAR(300);
  BEGIN
     
    DYNSQLSTATEMENT := 'UPDATE Production.ProductListPriceHistory
   SET
      ListPrice = ' || CAST(:NEWPRICE AS VARCHAR(10)) || '
   WHERE
      ProductID = ' || CAST(:PRODUCTID AS VARCHAR(10)) || '
      AND EndDate IS NULL;';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :DYNSQLSTATEMENT;
  END;
$$;

 CREATE PROCEDURE GetVacationHours  
   @employeeId INT,  
   @vacationHours INT OUTPUT  
AS  
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

 CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
      RETURN VACATIONHOURS;
   END;
$$;

 CREATE PROCEDURE OPTIONAL_PARAMETER @VAR1 INT = 1, @VAR2 INT = 2
AS
    BEGIN
        RETURN NULL;
    END

GO

EXEC OPTIONAL_PARAMETER @VAR2 = 4

 CREATE OR REPLACE PROCEDURE OPTIONAL_PARAMETER (VAR1 INT DEFAULT 1, VAR2 INT DEFAULT 2)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        RETURN NULL;
    END;
$$;

CALL OPTIONAL_PARAMETER(VAR2 => 4);

CREATE OR ALTER PROCEDURE GetVacationHours
   @employeeId INT,  
   @vacationHours INT OUTPUT
WITH EXECUTE AS OWNER
AS
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

 CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS OWNER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
      RETURN VACATIONHOURS;
   END;
$$;

 CREATE OR ALTER PROCEDURE GetVacationHours
   @employeeId INT,  
   @vacationHours INT OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

 CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
      RETURN VACATIONHOURS;
   END;
$$;

 CREATE OR ALTER PROCEDURE GetVacationHours
   @Param1 INT READONLY,  
   @Param2 INT VARYING
AS
BEGIN  
   SELECT * FROM Table1;
END;

 CREATE OR REPLACE PROCEDURE GetVacationHours (PARAM1 INT !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'READONLY PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!, PARAM2 INT !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'VARYING PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!)
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
         *
      FROM
         Table1);
      RETURN TABLE(ProcedureResultSet);
   END;
$$;

 CREATE OR REPLACE TABLE vEmployee (
	PersonID INT,
	LastName VARCHAR(255),
	FirstName VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES
    (1, 'AA', 'A'),
    (2, 'BB', 'B'),
    (3, 'CC', 'C'),
    (4, 'DD', 'D'),
    (5, 'EE', 'E'),
    (6, 'FF', 'F'),
    (7, 'GG', 'G');

CREATE OR REPLACE PROCEDURE CursorExample ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		CURSORVAR CURSOR
		FOR
			SELECT FirstName
			FROM vEmployee;
		FIRSTNAME VARCHAR;
		ProcedureResultSet RESULTSET;
	BEGIN
		 
		 
		OPEN CURSORVAR;
		FETCH
			CURSORVAR
		INTO
			:FIRSTNAME;
		FETCH
			CURSORVAR
		INTO
			:FIRSTNAME;
		CLOSE CURSORVAR;
		ProcedureResultSet := (
		SELECT
			:FIRSTNAME);
		RETURN TABLE(ProcedureResultSet);
	END;
$$;

 DECLARE @find VARCHAR(30);
DECLARE @find2 VARCHAR(30) = 'Default';
DECLARE @var VARCHAR(5), @var2 varchar(5);

 DECLARE
FIND VARCHAR(30);
FIND2 VARCHAR(30) := 'Default';
VAR VARCHAR(5);
VAR2 VARCHAR(5);
BEGIN
RETURN '';
END;

 DECLARE @MyTableVar TABLE(  
    column1 varchar(10));

 BEGIN
    DECLARE
        T_MYTABLEVAR TABLE(
            column1 VARCHAR(10));
END;

 DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;

 DECLARE
_GROUP VARCHAR(50);
SALES NUMBER(38, 4);
BEGIN
_GROUP := 'North America';
SALES := 2000000;
END;

 DECLARE @Group nvarchar(50), @Sales MONEY;

 DECLARE
_GROUP VARCHAR(50);
SALES NUMBER(38, 4);
BEGIN
RETURN '';
END;

 CREATE OR ALTER PROCEDURE AddPrivacyDepartment
AS 
EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');

 CREATE OR REPLACE PROCEDURE AddPrivacyDepartment ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO HumanResources.Department VALUES ('Privacy', 'Executive General and Administration', default);';
END;
$$;

 EXECUTE AddPrivacyDepartment;
SELECT DepartmentID, Name, GroupName FROM HumanResources.Department; 

 CALL AddPrivacyDepartment();

SELECT
DepartmentID,
Name,
GroupName
FROM
HumanResources.Department;

 CREATE OR ALTER PROCEDURE SetNewPrice @ProductID INT, @NewPrice MONEY
AS
  DECLARE @dynSqlStatement AS VARCHAR(300);
  SET @dynSqlStatement = 'UPDATE Production.ProductListPriceHistory SET ListPrice = ' + CAST(@NewPrice AS VARCHAR(10)) + ' WHERE ProductID = ' + CAST(@ProductID AS VARCHAR(10)) + ' AND EndDate IS NULL';
  EXECUTE (@dynSqlStatement);
GO

EXECUTE Set_New_Price @ProductID = 707, @NewPrice = 34.99;
SELECT ListPrice FROM Production.ProductListPriceHistory WHERE ProductID = 707 AND EndDate IS NULL;

 CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID INT, NEWPRICE NUMBER(38, 4))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    DYNSQLSTATEMENT VARCHAR(300);
  BEGIN
     
    DYNSQLSTATEMENT := 'UPDATE Production.ProductListPriceHistory
   SET
      ListPrice = ' || CAST(:NEWPRICE AS VARCHAR(10)) || '
   WHERE
      ProductID = ' || CAST(:PRODUCTID AS VARCHAR(10)) || '
      AND EndDate IS NULL;';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :DYNSQLSTATEMENT;
  END;
$$;

CALL Set_New_Price(707, 34.99);

SELECT
  ListPrice
FROM
  Production.ProductListPriceHistory
WHERE
  ProductID = 707 AND EndDate IS NULL;

 CREATE OR ALTER FUNCTION CalculateAveragePrice(@pid INT)
RETURNS MONEY
AS
BEGIN
  DECLARE @average AS MONEY;
  SELECT @average = AVG(LISTPRICE) FROM Production.ProductListPriceHistory WHERE ProductID = @pid;
  RETURN @average;
END;
GO

CREATE OR ALTER PROCEDURE SetNewPrice @ProductID INT
AS
  DECLARE @averageHistoricalPrice MONEY;
  EXECUTE @averageHistoricalPrice = [dbo].Calculate_Average_Price @pid=@ProductID;
  UPDATE Production.ProductListPriceHistory SET ListPrice = @averageHistoricalPrice WHERE ProductID =  @ProductID AND EndDate IS NULL;
GO

EXECUTE Set_New_Price @ProductID = 707;
SELECT ListPrice FROM Production.ProductListPriceHistory WHERE ProductID = 707 AND EndDate IS NULL;

 CREATE OR REPLACE FUNCTION CalculateAveragePrice (PID INT)
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
  WITH CTE1 AS
  (
    SELECT
      AVG(LISTPRICE) AS AVERAGE FROM
      Production.ProductListPriceHistory
    WHERE
      ProductID = PID
  )
  SELECT
    AVERAGE
  FROM
    CTE1
$$;

CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    AVERAGEHISTORICALPRICE NUMBER(38, 4);
  BEGIN
     
    CALL dbo.Calculate_Average_Price(:PRODUCTID);
    UPDATE Production.ProductListPriceHistory
      SET
        ListPrice = :AVERAGEHISTORICALPRICE
      WHERE
        ProductID = :PRODUCTID
        AND EndDate IS NULL;
  END;
$$;

CALL Set_New_Price(707);

SELECT
  ListPrice
FROM
  Production.ProductListPriceHistory
WHERE
  ProductID = 707 AND EndDate IS NULL;

 CREATE OR REPLACE PROCEDURE IfElseDemoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VALUE INT;
    BEGIN
         
        VALUE := 7;
        IF (:VALUE < 5) THEN
            VALUE := 100;
        ELSEIF (:VALUE >= 5 AND :VALUE < 10) THEN
            BEGIN
                VALUE := 300;
                VALUE := :VALUE - 100;
            END;
        ELSE
            VALUE := -1;
        END IF;
        RETURN :VALUE;
    END;
$$;

DECLARE
    RESULT INT;
BEGIN
    CALL IfElseDemoProcedure();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
    PRINT @result;
END;

 DECLARE @maxWeight FLOAT, @productKey INTEGER  
SET @maxWeight = 100.00  
SET @productKey = 424  
IF @maxWeight <= 99  
    SELECT @productKey,  'This product is too heavy to ship and is only available for pickup.' 
ELSE  
    SELECT @productKey, 'This product is available for shipping or pickup.' 

 DECLARE
    MAXWEIGHT FLOAT;
    PRODUCTKEY INTEGER;
    BlockResultSet1 VARCHAR;
    BlockResultSet2 VARCHAR;
    return_arr ARRAY := array_construct();
BEGIN
    MAXWEIGHT := 100.00;
    PRODUCTKEY := 424;
    IF (:MAXWEIGHT <= 99) THEN
        BlockResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:BlockResultSet1) AS
            SELECT
                :PRODUCTKEY,  'This product is too heavy to ship and is only available for pickup.';
        return_arr := array_append(return_arr, :BlockResultSet1);
    ELSE
        BlockResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:BlockResultSet2) AS
            SELECT
                :PRODUCTKEY, 'This product is available for shipping or pickup.';
        return_arr := array_append(return_arr, :BlockResultSet2);
    END IF;
    --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
    RETURN return_arr;
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;
        RETURN 1;

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

    END;
$$;

 -- Procedure with output parameter
CREATE PROCEDURE dbo.outmain
@name VARCHAR (255) OUTPUT
AS
SET @name = 'Jane';

-- Auxiliary procedure that calls the main procedure
CREATE PROCEDURE dbo.outaux
AS
DECLARE @name VARCHAR (255);
EXEC dbo.outmain
    @name = @name OUTPUT;