MULTI-STATEMENT TABLE-VALUED

Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.

Description

Multi-statement table-valued is similar to Inline-statement table-valued (INLINE TABLE-VALUED). However Multi-statement table-valued may have more than one statement in its function body, the table columns are specified in the return type and it has a BEGIN/END block (SQL Server Language Reference Creating a multi-statement table-valued function

SQL Server Syntax

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [READONLY] }
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Snowflake SQL

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'

Sample Source Patterns

The following section describes all the possible source code patterns that can appear in this kind of CREATE FUNCTION syntax.

The function body of Multi-Statement Table-Valued function must be a SELECT statement. For this reason the others statements must be called separately.

Insert values in a table

Inserts one or more rows into the table and returns the table with the new values

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION calc_behavioral_segment()
RETURNS @behavioral_segments TABLE (behavioral_segment VARCHAR(50))
AS
BEGIN
	DECLARE @col varchar(15)
	SET @col = 'Unknown'
	INSERT INTO @behavioral_segments 
	SELECT @col
	
	RETURN 
END

SELECT * FROM calc_behavioral_segment();

Result

BEHAVIORAL_SEGMENT|
------------------+
           Unknown|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE FUNCTION calc_behavioral_segment()
RETURNS TABLE(behavioral_segment VARCHAR(50))
AS
$$     
     WITH CTE AS (SELECT 'Unknown' as behavior)
     SELECT behavior FROM CTE
$$;

SELECT * FROM TABLE(calc_behavioral_segment());

Results

BEHAVIORAL_SEGMENT|
------------------+
           Unknown|

Insert value according to if/else statement

Inserts a row into the table according to the condition and returns the table with the new value

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION odd_or_even_number(@number INT)
RETURNS @numbers TABLE (number_type VARCHAR(15))
AS
BEGIN 
	IF ((@number % 2) = 0)
	BEGIN
		INSERT @numbers SELECT 'Even'
	END
		  
	ELSE
	BEGIN
		INSERT @numbers SELECT 'Odd'
	END

	RETURN
END

SELECT * FROM odd_or_even_number(9);

Result

       NUMBER_TYPE|
------------------+
               Odd|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE FUNCTION odd_or_even_number(number int)
RETURNS TABLE(number_type VARCHAR(50))
AS
$$
     WITH CTE2 as (SELECT CASE WHEN (number % 2) = 0 THEN 'EVEN'
     ElSE 'ODD'
     END AS result)
     SELECT result FROM CTE2
$$;

select * from table(odd_or_even_number(9));

Result

       NUMBER_TYPE|
------------------+
               Odd|

Inserts multiple according to if/else statement

The example below inserts more than one value into the table and more than one variable is modified according to the condition. Returns the table with the new values

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION new_employee_hired(@id VARCHAR (50), @position VARCHAR(50), @experience VARCHAR(15))
RETURNS @new_employee TABLE (id_employee VARCHAR (50), working_from_home BIT, team VARCHAR(15), computer VARCHAR(15)) 
AS
BEGIN 
	DECLARE @wfh BIT
	DECLARE @team VARCHAR(15)
	DECLARE @computer VARCHAR(15)

	IF @position = 'DEVELOPER'
	BEGIN
		SET @team = 'TEAM_1'
		SET @computer = 'LAPTOP'		
	END

	IF @position = 'IT'
	BEGIN		
		SET @team = 'TEAM_2'
		SET @computer = 'DESKTOP'
	END

	IF @experience = 'JUNIOR'
	BEGIN
		SET @wfh = '0'			
	END
	IF @experience = 'SENIOR'
	BEGIN
		SET @wfh = '1'						
	END

	INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
	RETURN
END

SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');

Result

   ID_EMPLOYEE|  WORKING_FROM_HOME|     TEAM|   COMPUTER|
--------------+-------------------|---------|-----------|
     123456789|                  1|   TEAM_1|     LAPTOP|

Snowflake

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE FUNCTION new_employee_hired(id VARCHAR (50), position VARCHAR(50), experience VARCHAR(15))
RETURNS TABLE (id_employee VARCHAR (50), working_from_home BOOLEAN, team VARCHAR(15), computer VARCHAR(15))
AS
$$
    WITH CTE1 AS (SELECT CASE 
            WHEN POSITION = 'DEVELOPER' THEN 'TEAM_1'
            WHEN POSITION = 'IT' THEN  'TEAM_2'
            END),
          CTE2 AS (SELECT CASE 
            WHEN POSITION = 'DEVELOPER' THEN 'LAPTOP'
            WHEN POSITION = 'IT' THEN  'DESKTOP'
            END),
          CTE3 AS (SELECT CASE 
            WHEN EXPERIENCE = 'JUNIOR' THEN FALSE
            WHEN EXPERIENCE = 'SENIOR' THEN TRUE
            END)
            SELECT ID, (SELECT * FROM CTE3), (SELECT * FROM CTE1), (SELECT * FROM CTE2)                  
$$; 

SELECT * FROM TABLE(new_employee_hired('123456789', 'DEVELOPER', 'SENIOR'));

Result

   ID_EMPLOYEE|  WORKING_FROM_HOME|     TEAM|   COMPUTER|
--------------+-------------------|---------|-----------|
     123456789|                  1|   TEAM_1|     LAPTOP|

In case there are nested if statements and more than one variables are modified in the statements it is necessary to use a stored procedure.

Update values previously inserted

Updates columns values of the table into the function body and returns it with the new values.

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION get_employees_history()
RETURNS @employee_history TABLE (
	department_name NVARCHAR(50),
	first_name NVARCHAR(50),
	last_name NVARCHAR(50), 
	start_date DATE,
	end_date DATE,
	job_title NVARCHAR(50), 
	months_working INT
)
BEGIN
	INSERT INTO @employee_history
	SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
	LEFT OUTER JOIN employee_department_history EH
		ON D.department_ID = EH.department_ID
	INNER JOIN  Employee E
		ON E.business_entity_ID = EH.business_entity_ID
	INNER JOIN Person P
		ON P.business_entity_ID = E.business_entity_ID 
		

	UPDATE @employee_history
	SET 
		months_working = 
		CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
		ELSE DATEDIFF(MONTH, start_date, end_date)
	END
	RETURN;
END;

SELECT TOP(10) * FROM get_employees_history();

Result

                      DEPARTMENT_NAME|         FIRST_NAME|          LAST_NAME|      START_DATE|         END_DATE|                           JOB_TITLE|        MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
                                Sales|               Syed|              Abbas|      2013-03-14| 	    NULL|               Pacific Sales Manager|                   106|
                           Production|                Kim|        Abercrombie|      2010-01-16|             NULL|        Production Technician - WC60|                   144|
                    Quality Assurance|              Hazem|           Abolrous|      2009-02-28|             NULL|           Quality Assurance Manager|                   155|
               Shipping and Receiving|              Pilar|           Ackerman|      2009-01-02|             NULL|   Shipping and Receiving Supervisor|                   156|
                           Production|                Jay|              Adams|      2009-03-05|             NULL|        Production Technician - WC60|                   154|
                 Information Services|           François|           Ajenstat|      2009-01-17|             NULL|              Database Administrator|                   156|
                                Sales|                Amy|            Alberts|      2012-04-16|             NULL|              European Sales Manager|                   117|
                           Production|               Greg|           Alderson|      2008-12-02|             NULL|        Production Technician - WC45|                   157|
                    Quality Assurance|               Sean|          Alexander|      2008-12-28|             NULL|        Quality Assurance Technician|                   157|
           Facilities and Maintenance|               Gary|             Altman|      2009-12-02|             NULL|                  Facilities Manager|                   145|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE FUNCTION get_employees_history()
RETURNS TABLE(
    department_name VARCHAR(50),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    start_date DATE,
    end_date DATE,
    job_title VARCHAR(50),
    months_working INT)
AS
$$
    WITH CTE AS (
      SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 
        CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE()) 
        ELSE DATEDIFF(MONTH, start_date, end_date) END as months_working 
      FROM Department D
      LEFT OUTER JOIN employee_department_history  EH
          ON D.department_ID = EH.department_ID
      INNER JOIN  Employee E
          ON E.business_entity_ID = EH.business_entity_ID
      INNER JOIN Person P
          ON P.business_entity_ID = E.business_entity_ID
    )
    SELECT * FROM CTE
   
$$;

SELECT TOP 10 * FROM TABLE(get_employees_history());

Result

                      DEPARTMENT_NAME|         FIRST_NAME|          LAST_NAME|      START_DATE|         END_DATE|                           JOB_TITLE|        MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
                                Sales|               Syed|              Abbas|      2013-03-14| 	    NULL|               Pacific Sales Manager|                   106|
                           Production|                Kim|        Abercrombie|      2010-01-16|             NULL|        Production Technician - WC60|                   144|
                    Quality Assurance|              Hazem|           Abolrous|      2009-02-28|             NULL|           Quality Assurance Manager|                   155|
               Shipping and Receiving|              Pilar|           Ackerman|      2009-01-02|             NULL|   Shipping and Receiving Supervisor|                   156|
                           Production|                Jay|              Adams|      2009-03-05|             NULL|        Production Technician - WC60|                   154|
                 Information Services|           François|           Ajenstat|      2009-01-17|             NULL|              Database Administrator|                   156|
                                Sales|                Amy|            Alberts|      2012-04-16|             NULL|              European Sales Manager|                   117|
                           Production|               Greg|           Alderson|      2008-12-02|             NULL|        Production Technician - WC45|                   157|
                    Quality Assurance|               Sean|          Alexander|      2008-12-28|             NULL|        Quality Assurance Technician|                   157|
           Facilities and Maintenance|               Gary|             Altman|      2009-12-02|             NULL|                  Facilities Manager|                   145|

Multiple return clauses

In the following sample there is more than one return clause, this is because depending on the situation it is not necessary to keep executing the whole function.

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION create_new_team(@team_name VARCHAR(50))
RETURNS @new_team TABLE (type VARCHAR(50), name VARCHAR(50))
AS
BEGIN 
	DECLARE @employees INT
	SET @employees = (SELECT count(*) FROM employee)
	DECLARE @type VARCHAR(15)
	SET @type = 'small_team'
	IF (@employees < 8)
	BEGIN
		INSERT @new_team VALUES (@type, @team_name) 
		RETURN
	END
	
	SET @type = 'big_team'
	INSERT @new_team VALUES (@type, @team_name) 

	RETURN
END

SELECT * FROM create_new_team('Team1');             

Result

          TYPE|           NAME|
--------------+ --------------+
    SMALL_TEAM|          TEAM1|
 

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE FUNCTION create_new_team(team_name VARCHAR(50))
RETURNS TABLE(type VARCHAR(50), name VARCHAR(50))
AS
$$   
     WITH CTE1 AS (SELECT COUNT(*) AS employees FROM EMPLOYEE),
     CTE2 AS (SELECT CASE WHEN(SELECT employees FROM cte1) < 8 THEN 'small_team' ELSE'big_team' END AS type)
     SELECT (SELECT type FROM CTE2), TEAM_NAME
$$;

SELECT * FROM TABLE(create_new_team('Team1'));

Result

          TYPE|           NAME|
--------------+ --------------+
    SMALL_TEAM|          TEAM1|

This transformation is applied when there is only one value to insert, if there is more than one value it is necessary to use a stored procedure.

Complex cases

The example is a complex case that uses nested if statements and inserts a value depending on the true condition.

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION vacation_status(@id VARCHAR (50))
RETURNS @status TABLE (vacation_status VARCHAR(30))
AS
BEGIN 
	DECLARE @hire_date DATETIME
	SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
	DECLARE @vacation_hours INT
	SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
	DECLARE @time_working INT
	SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))

	IF (@vacation_hours > 0)
	BEGIN
		IF (@time_working > 3)
		BEGIN
			IF (@vacation_hours < 120)
			BEGIN
				INSERT INTO @status VALUES ('Ok')
			END

			IF (@vacation_hours = 120)
			BEGIN
				INSERT INTO @status values ('In the limit')
			END

			IF (@vacation_hours > 120)
			BEGIN
				INSERT INTO @status VALUES ('With excess')
			END
		END
		ELSE
		BEGIN
			INSERT INTO @status values ('Hired recently')
		END
	END
	ELSE
	BEGIN
		INSERT INTO @status values ('No hours')
	END
	RETURN
END

SELECT * FROM vacation_status('adventure-worksken0')

Result

  VACATION_STATUS|
-----------------+
               OK|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE FUNCTION vacation_status (ID VARCHAR(50))
RETURNS TABLE (vacation_status varchar(30))
AS
$$
     WITH CTE1 AS (SELECT hiredate AS hire_date FROM EMPLOYEE WHERE employeeId = ID),
     CTE2 AS (SELECT COUNT(vacationhours) AS vacation_hours FROM EMPLOYEE WHERE loginid = ID),
     CTE3 AS (SELECT DATEDIFF(MONTH, (SELECT * from CTE1), GETDATE()) AS time_working),
     CTE4 AS (SELECT CASE 
            WHEN (SELECT vacation_hours FROM CTE2) > 0 THEN (
                    SELECT CASE 
                        WHEN (SELECT time_working FROM CTE3) > 3 THEN (
                                SELECT CASE
                                  WHEN (SELECT vacation_hours FROM CTE2) < 120 THEN 'Ok'
                                  WHEN (SELECT vacation_hours FROM CTE2) = 120 THEN 'On the limit'
                                  WHEN (SELECT vacation_hours FROM CTE2) > 120 THEN 'With excess'                                                                                                                       
                                  END
                               )   
              ELSE 'Hired recently'
              END
                )                                                          
            ELSE 'No hours'
            END AS result) 
            SELECT result FROM CTE4
$$;

SELECT * FROM TABLE(vacation_status('adventure-worksken0'));

Second Tab

  VACATION_STATUS|
-----------------+
               OK|

Known Issues

While statements along side queries

The problem with this example is that there's no way of transforming the while statement to a CTE inside the WITH clause of the main select, this forces us to transform this statement to store procedure to maintain the same logic.

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION get_group_name
(@department_id INT)
RETURNS @group_names TABLE (group_name VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'  
WHILE @name = 'Another Department'   
BEGIN    
	IF (@department_id < 3)
	BEGIN
		SET @name = 'engineering'
	END
	  
	IF @department_id = 3
	BEGIN
		SET @name = 'Tool Design'
	END
	
	SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END

SELECT * FROM get_group_name(9);

Result

        GROUP_NAME|
------------------+
       Tool Design|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE PROCEDURE number_type(department_id FLOAT) 
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER 
AS 
$$ 
    // REGION SnowConvert Helpers Code
   var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
   var SELECT = (sql,binds = [],...args) => {
      var reducers = args.filter((i) => i instanceof Function);
      reducers = reducers.length ? reducers : [(value) => value]
      args = args.splice(0,args.length - reducers.length)
      EXEC("SELECT " + sql,binds)
      if (ROW_COUNT < 1) return;
      var colCount = _ROWS.getColumnCount();
      if (colCount != reducers.length) throw new Error("Missing arguments results has ${colCount} columns");
      var cols = Array.from(Array(colCount),() => []);
      while ( _ROWS.next() ) {
         for(var i = 0;i < colCount;i++) {
            cols[i].push(_ROWS.getColumnValue(i + 1))
         }
      }
      if (colCount == 1) {
         cols[0].forEach((value) => reducers[0](value))
         return (cols[0])[0];
      }
      for(var i = 0;i < colCount;i++) {
         cols[i].forEach((value) => reducers[i](value))
      }
   };
   var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME  = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
   function* sqlsplit(sql) {
      var part = '';
      var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
      for(var i = 0;i < sql.length;i++) {
         if (sql[i] == ';') {
            yield part + sql[i];
            part = '';
         } else if (ismark()) {
            part += sql[i++] + sql[i++];
            while ( i < sql.length && !ismark() ) {
               part += sql[i++];
            }
            part += sql[i] + sql[i++];
         } else part += sql[i];
      }
      if (part.trim().length) yield part;
   };
   var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
   var fixBind = function (arg) {
      arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
      return arg;
   };
   var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
      binds = binds ? binds.map(fixBind) : binds;
      for(var stmt of sqlsplit(stmt)) {
         try {
            _RS = snowflake.createStatement({
                  sqlText : stmt,
                  binds : binds
               });
            _ROWS = _RS.execute();
            ROW_COUNT = _RS.getRowCount();
            NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
            return {
               THEN : (action) => !SQLCODE && action(fetch(_ROWS))
            };
         } catch(error) {
            let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
            let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
            MESSAGE_TEXT = error.message.toString();
            SQLCODE = error.code.toString();
            SQLSTATE = error.state.toString();
            snowflake.execute({
               sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
               binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
            });
            throw error;
         }
      }
   };
   // END REGION

   EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_group_names(group_name VARCHAR(30));`);
   let NAME = `Another Department`;
   while ( NAME == `Another Department` ) {
      if (DEPARTMENT_ID < 3) {
         {
            NAME = `engineering`;
         }
      }
      if (DEPARTMENT_ID == 3) {
         {
            NAME = `Tool Design`;
         }
      }
      // ** MSC-INFORMATION - MSCINF0016 - SELECTION OF @department_id MAY HAVE RUNTIME ERRORS **
      SELECT(`? / 3`,[DEPARTMENT_ID],(value) => DEPARTMENT_ID = value);
   }
   EXEC(`INSERT INTO T_group_names SELECT ?`,[NAME]);
   EXEC(`SELECT * from T_group_names`);
   return NAME;
$$; 

CALL number_type(9);
SELECT * FROM T_group_names;

Result

        GROUP_NAME|
------------------+
       Tool Design|

Declare Cursor

User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR. Use a Stored Procedure to work with cursors.

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER FUNCTION amount_new_specimens(@id int)
RETURNS @new_specimens TABLE (amount int)
AS
BEGIN
	DECLARE @first_specimen VARCHAR(30) ;
	set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
	DECLARE @second_specimen VARCHAR(30);

	DECLARE @specimens TABLE (name_specimen VARCHAR(30))

	DECLARE Cursor1 CURSOR 
	FOR SELECT name_specimen 
	FROM specimen 

	OPEN cursor1
	FETCH NEXT FROM cursor1
	INTO @second_specimen;
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		IF @first_specimen <> @second_specimen
		BEGIN
			INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
		END
		FETCH NEXT FROM cursor1
		INTO @second_specimen;
	END

	CLOSE cursor1;
	DEALLOCATE cursor1;

	INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
	RETURN 
END

SELECT * FROM amount_new_specimens(1);

Result

            AMOUNT|
------------------+
                 3|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE PROCEDURE amount_new_specimens(ID float)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
      // REGION SnowConvert Helpers Code
   var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
   var SELECT = (sql,binds = [],...args) => {
      var reducers = args.filter((i) => i instanceof Function);
      reducers = reducers.length ? reducers : [(value) => value]
      args = args.splice(0,args.length - reducers.length)
      EXEC("SELECT " + sql,binds)
      if (ROW_COUNT < 1) return;
      var colCount = _ROWS.getColumnCount();
      if (colCount != reducers.length) throw new Error("Missing arguments results has ${colCount} columns");
      var cols = Array.from(Array(colCount),() => []);
      while ( _ROWS.next() ) {
         for(var i = 0;i < colCount;i++) {
            cols[i].push(_ROWS.getColumnValue(i + 1))
         }
      }
      if (colCount == 1) {
         cols[0].forEach((value) => reducers[0](value))
         return (cols[0])[0];
      }
      for(var i = 0;i < colCount;i++) {
         cols[i].forEach((value) => reducers[i](value))
      }
   };
   var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME  = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
   function* sqlsplit(sql) {
      var part = '';
      var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
      for(var i = 0;i < sql.length;i++) {
         if (sql[i] == ';') {
            yield part + sql[i];
            part = '';
         } else if (ismark()) {
            part += sql[i++] + sql[i++];
            while ( i < sql.length && !ismark() ) {
               part += sql[i++];
            }
            part += sql[i] + sql[i++];
         } else part += sql[i];
      }
      if (part.trim().length) yield part;
   };
   var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
   var fixBind = function (arg) {
      arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
      return arg;
   };
   var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
      binds = binds ? binds.map(fixBind) : binds;
      for(var stmt of sqlsplit(stmt)) {
         try {
            _RS = snowflake.createStatement({
                  sqlText : stmt,
                  binds : binds
               });
            _ROWS = _RS.execute();
            ROW_COUNT = _RS.getRowCount();
            NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
            return {
               THEN : (action) => !SQLCODE && action(fetch(_ROWS))
            };
         } catch(error) {
            let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
            let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
            MESSAGE_TEXT = error.message.toString();
            SQLCODE = error.code.toString();
            SQLSTATE = error.state.toString();
            snowflake.execute({
               sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
               binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
            });
            throw error;
         }
      }
   };
   var CURSOR = function (stmt,binds) {
      var statementObj, result_set, total_rows, isOpen = false, result_set_table = '', self = this;
      this.CURRENT = new Object;
      this.OPEN = function (usingParams) {
            try {
               if (usingParams) binds = usingParams;
               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);
            }
            return this;
         };
      this.CURSOR_ROWS = function () {
            return total_rows;
         };
      this.FETCH_STATUS = function () {
            return total_rows >= row_count;
         };
      this.FETCH_NEXT = function () {
            self.res = [];
            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 = row_count = result_set_table = total_rows = result_set = statementObj = undefined;
         };
      this.DEALLOCATE = function () {
            this.CURRENT = self = undefined;
         };
   };
   // END REGION

   EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_new_specimens(amount int);`);
   let FIRST_SPECIMEN;
   FIRST_SPECIMEN = SELECT(`name_specimen from specimen where specimen_id = ?`,[ID]);
   let SECOND_SPECIMEN;
   EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_specimens (name_specimen VARCHAR(30))`);
   var CURSOR1 = new CURSOR(`SELECT name_specimen FROM specimen`,[],false);
   CURSOR1.OPEN();
   CURSOR1.FETCH_NEXT() && ([SECOND_SPECIMEN] = CURSOR1.INTO());
   while ( CURSOR1.FETCH_STATUS() ) {
      if (FIRST_SPECIMEN != SECOND_SPECIMEN) {
         {
            EXEC(`INSERT INTO T_specimens values (CONCAT_WS('-', ?, ?))`,[FIRST_SPECIMEN,SECOND_SPECIMEN]);
         }
      }
      CURSOR1.FETCH_NEXT() && ([SECOND_SPECIMEN] = CURSOR1.INTO());
   }
   CURSOR1.CLOSE();
   CURSOR1.DEALLOCATE();
   EXEC(`INSERT INTO T_new_specimens SELECT COUNT(*) FROM T_specimens`);
   return;
$$;

CALL amount_new_specimens(1);
SELECT * FROM T_new_specimens;

Result

            AMOUNT|
------------------+
                 3|

Different statements are not supported in Common Tables Expressions

The clauses UPDATE, INSERT, DELETE, ALTER or DROP are not supported on the body of common tables expressions, even after their declaration using a delimitator. For this reason, the function can be modified to work as a stored procedure.

SQL Server

MULTI-STATEMENT TABLE-VALUED

CREATE OR ALTER PROCEDURE product_history 
AS
BEGIN
	DECLARE @product_history TABLE (
		product_name NVARCHAR(50),
		rating INT
	)
	INSERT INTO @product_history
	SELECT P.Name AS product_name, AVG(ALL R.rating) FROM Production.product P
	INNER JOIN  Production.product_review R
		ON R.product_ID = P.product_ID
	GROUP BY P.Name;

	DELETE FROM @product_history
	WHERE rating < 2;

	SELECT * FROM @product_history; 

END
GO;

EXEC product_history

Result

                      PRODUCT_NAME|            Rating|
----------------------------------+------------------|
                 HL Mountain Pedal|                 3|
            Mountain Bike Socks, M|                 5|
             Road-550-W Yellow, 40|                 5|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

CREATE OR REPLACE PROCEDURE product_history ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  // REGION SnowConvert Helpers Code
  var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME  = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
  function* sqlsplit(sql) {
    var part = '';
    var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
    for(var i = 0;i < sql.length;i++) {
      if (sql[i] == ';') {
        yield part + sql[i];
        part = '';
      } else if (ismark()) {
        part += sql[i++] + sql[i++];
        while ( i < sql.length && !ismark() ) {
          part += sql[i++];
        }
        part += sql[i] + sql[i++];
      } else part += sql[i];
    }
    if (part.trim().length) yield part;
  };
  var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
  var fixBind = function (arg) {
    arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
    return arg;
  };
  var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
    binds = binds ? binds.map(fixBind) : binds;
    for(var stmt of sqlsplit(stmt)) {
      try {
        _RS = snowflake.createStatement({
            sqlText : stmt,
            binds : binds
          });
        _ROWS = _RS.execute();
        ROW_COUNT = _RS.getRowCount();
        NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
        return {
          THEN : (action) => !SQLCODE && action(fetch(_ROWS))
        };
      } catch(error) {
        let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
        let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
        MESSAGE_TEXT = error.message.toString();
        SQLCODE = error.code.toString();
        SQLSTATE = error.state.toString();
        snowflake.execute({
          sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
          binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
        });
        throw error;
      }
    }
  };
  // END REGION

  EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_product_history (
        product_name VARCHAR(50),
        rating INT
    )`);
  EXEC(`	
    INSERT INTO .T_product_history
        SELECT P.Name AS product_name,
        AVG(ALL R.rating)  FROM Production.product P
        INNER JOIN Production.product_review R
            ON R.product_ID = P.product_ID GROUP BY P.Name`);
  EXEC(`DELETE FROM T_product_history WHERE rating < 2`);
  EXEC(`SELECT * FROM .T_product_history`);
$$;

CALL product_history ()
SELECT * FROM T_product_history;

Result

                      PRODUCT_NAME|            Rating|
----------------------------------+------------------|
                 HL Mountain Pedal|                 3|
            Mountain Bike Socks, M|                 5|
             Road-550-W Yellow, 40|                 5|

Related EWIs

1. MSCEWI1068: User defined function was transformed to a Snowflake procedure.