SnowConvert: DDLs do Transact

Referência de conversão para todas as instruções DDL que são precedidas pela palavra CREATE.

Índice

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Aviso

Atualmente, a instrução _ Create Index _ não está sendo convertida, mas está sendo analisada. Além disso, se o seu código-fonte tiver instruções Create index, elas serão contabilizadas no _ Relatório de avaliação. _

Exemplo de criação de índice

 CREATE INDEX my_index_name ON my_table (column1, column2);
Copy

Nota

Por motivos arquitetônicos, o Snowflake não oferece suporte a índices, portanto, o SnowConvert removerá todo o código relacionado à criação de índices. O Snowflake cria automaticamente micropartições para cada tabela que ajudam a acelerar o desempenho das operações de DML. O usuário não precisa se preocupar em criar ou gerenciar essas micropartições.

Normalmente, isso é suficiente para ter um desempenho de consulta muito bom; no entanto, há maneiras de melhorá-lo criando chaves de clustering de dados. A página oficial do Snowflake fornece mais informações sobre micropartições e clustering de dados.

Exibição materializada

Applies to
  • [x] Azure Synapse Analytics

Descrição

No Snowconvert, as exibições materializadas são transformadas em tabelas dinâmicas do Snowflake. Para configurar corretamente as tabelas dinâmicas, dois parâmetros essenciais devem ser definidos: TARGET_LAG e WAREHOUSE. Se esses parâmetros não forem especificados nas opções de configuração, o Snowconvert usará como padrão os valores predefinidos durante a conversão, conforme demonstrado no exemplo abaixo.

Para obter mais informações sobre exibições materializadas, clique aqui.

Para obter detalhes sobre os parâmetros necessários para as tabelas dinâmicas, clique aqui.

Amostra de padrões da origem

SqlServer

 CREATE MATERIALIZED VIEW sales_total
AS
SELECT SUM(amount) AS total_sales
FROM sales;
Copy

Snowflake

 CREATE OR REPLACE DYNAMIC TABLE sales_total
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
AS
SELECT SUM(amount) AS total_sales
FROM
sales;
Copy

Problemas conhecidos

Nenhum erro conhecido foi detectado até o momento.

Procedimentos

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

1. CREATE PROCEDURE Translation

O Snowflake CREATE PROCEDURE é definido na sintaxe SQL, enquanto suas instruções internas são definidas em JavaScript.

Código-fonte:

 -- Additional Params: -t JavaScript
CREATE PROCEDURE HumanResources.uspGetAllEmployees
     @FirstName NVARCHAR(50),
     @Age INT
AS
    -- TSQL Statements and queries...
GO
Copy

Código convertido:

 CREATE OR REPLACE PROCEDURE HumanResources.uspGetAllEmployees (FIRSTNAME STRING, AGE INT)
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.
$$;
Copy

Tipos de dados de parâmetros

Os tipos de dados dos parâmetros estão sendo convertidos para o equivalente no Snowflake. Consulte também Tipos de dados.

Auxiliar EXEC

Para que seja possível executar instruções de um procedimento no ambiente SnowFlake, essas instruções precisam ser pré-processadas e adaptadas para refletir sua execução em diversas variáveis específicas da linguagem de origem.

O SnowConvert converte automaticamente as instruções suportadas e usa um auxiliar EXEC. Esse auxiliar fornece recursos de acesso e atualização a muitas variáveis que simulam como seria a execução dessas instruções em seu ambiente nativo.

Por exemplo, você pode ver que, nos procedimentos migrados, há um bloco de código que é sempre adicionado. Na próxima seção, explicaremos a estrutura básica desse código. Lembre-se de que estamos sempre avaliando e buscando maneiras novas e aprimoradas de agilizar as transformações e qualquer auxiliar que precisarmos.

Estrutura

A estrutura básica do auxiliar EXEC é a seguinte:

  1. Seção de declaração de variáveis: Aqui, declaramos as diferentes variáveis ou objetos que conterão valores associados à execução das instruções dentro do procedimento. Isso inclui valores como o número de linhas afetadas por uma instrução ou até mesmo o próprio conjunto de resultados.

  2. Declaração da função fixBind: Esta é uma função auxiliar usada para corrigir vinculações quando elas são do tipo Date.

  3. Declaração da função EXEC: Esta é a principal função do auxiliar EXEC. Ela recebe a instrução a ser executada, a matriz de vinculações (basicamente as variáveis ou os parâmetros que podem ser modificados pela execução e que exigem a permanência dos dados durante toda a execução do procedimento), o sinalizador noCatch que determina se ERROR_HANDLERS deve ser usado e a função catchFunction para executar o código personalizado quando houver uma exceção na execução da instrução. O corpo da função EXEC é muito simples: executa a instrução e armazena todos os dados valiosos produzidos por sua execução, tudo dentro de um bloco de tratamento de erros.

  4. ERROR VARS: O bloco de captura EXEC configura uma lista de variáveis de erro, como MESSAGE_TEXT, SQLCODE, SQLSTATE, PROC_NAME e ERROR_LINE, que podem ser usadas para recuperar valores de funções definidas pelo usuário, a fim de emular o comportamento das funções integradas do SQL Server ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE e ERROR_STATE . Depois que todas essas variáveis forem definidas com um valor, a função definida pelo usuário UPDATE_ERROR_VARS será responsável por atualizar algumas variáveis de ambiente com os valores de erro, para ter acesso a elas no escopo SQL.

Código

O bloco de código a seguir representa o auxiliar EXEC dentro de um procedimento:

    var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, NUM_ROWS_AFFECTED, INTO;
   var fixBind = function (arg) {
      arg = arg == undefined ? null : arg instanceof Date ? arg.toISOString() : arg;
      return arg;
   };
   var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
   var EXEC = (stmt,binds = [],noCatch = false) => {
      binds = binds ? binds.map(fixBind) : binds;
      for(var stmt of stmt.split(";").filter((_) => _)) {
         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]});
            throw error;
         }
      }
   };
Copy

Exemplo simples de EXEC

Este é um exemplo simples de uma chamada EXEC dentro de um procedimento armazenado

Código-fonte

 -- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_1
AS
	EXECUTE('SELECT 1 AS Message');
GO
Copy
 -- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_1
GO
Copy

Código esperado

 CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_1 ()
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
	var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME  = 'dbo', 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(`SELECT 1 AS Message;`);
$$;
Copy

EXEC em um procedimento armazenado com um parâmetro

Neste exemplo, o comando EXEC está dentro de um procedimento armazenado e recebe um valor de parâmetro

Código-fonte

 -- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_2
	@p1 varchar(50) = N''
AS
	EXEC ('SELECT ' + @p1);
GO
Copy
 -- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_2 N'''Hello World!'''
GO
Copy

Código esperado

CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_2 (P1 STRING DEFAULT '')
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(`SELECT
   ${P1};`);
$$;
Copy

EXEC invocando um procedimento armazenado com um parâmetro

Neste exemplo, o EXEC invoca outro procedimento armazenado e adiciona um parâmetro

Código-fonte

 -- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_3
	@p1 varchar(50) = N''
AS
	EXEC EXEC_EXAMPLE_2 @p1
GO
Copy
 -- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_3 N'''Hello World!'''
GO
Copy

Código esperado

 CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_3 (P1 STRING DEFAULT '')
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(`CALL EXEC_EXAMPLE_2(?)`,[P1]);
$$;
Copy

Parâmetros com valor padrão.

No SqlServer, pode haver parâmetros com um valor padrão, caso não sejam especificados quando um procedimento estiver sendo chamado.

Por exemplo

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

No Snowflake é convertido para

 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
$$
    .
    .
    .
$$;
Copy
 CALL PROC_WITH_DEFAULT_PARAMS1(param2 => 10, param4 => 15);
Copy

auxiliar CURSOR

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

Auxiliar Insert Into EXEC

O auxiliar Insert into Exec gera uma função chamada Insert insertIntoTemporaryTable(sql). Essa função permitirá que a transformação de INSERT INTO TABLE_NAME EXEC(...) de TSQL para Snowflake imite o comportamento da instrução original, inserindo seus dados em uma tabela temporária e, em seguida, adicionando-os novamente ao Insert original.

Para obter mais informações sobre como o código para essa instrução é modificado, consulte a seção para o Insert Into Exec

Nota

Esse código gerado para INSERT INTO EXEC pode apresentar problemas de desempenho ao lidar com instruções EXECUTE que contêm várias consultas.

   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`)
Copy

Auxiliar LIKE

Caso uma expressão semelhante seja encontrada em um procedimento, por exemplo

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

Como o interior do procedimento é transformado em javascript, a expressão like gerará um erro. Para manter a funcionalidade, uma função é adicionada no início do procedimento se uma expressão semelhante for encontrada.

   function LIKE(expr,pattern,esc,cs) {
    function fixPattern(pattern,esc) {
      const specials = '/.*+?|(){}[]\\'.split('');
      var newPattern = "";
      var fix = (c) => specials.includes(c) ? '\\' + c : c;
      for(var i = 0;i < pattern.length;i++) {
        var c = pattern[i];
        if (c === esc) {
          newPattern += pattern[i + 1]
          i++
        } else if (c === '%') {
          newPattern += ".*?"
        } else if (c === '_') {
          newPattern += "."
        } else if (c === '[' || ']') {
          newPattern += c
        } else newPattern += fix(c)
      }
      return newPattern;
    }
    return new RegExp(`^${fixPattern(pattern,esc)}$`,cs ? '' : 'i').exec(expr) != null;
  }
Copy

Com essa função, podemos replicar a funcionalidade da expressão like do sql. Vejamos os diferentes casos em que ele pode ser usado

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

No último código, há um like normal, um not like e um like com escape. A transformação será

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

Observe que likes são transformados em chamadas de função

 LIKE(VARIABLEVALUE,`%c%`)
!LIKE(VARIABLEVALUE,`%c%`)
LIKE(VARIABLEVALUE,`%c!%%`,`!`)
Copy

Os parâmetros que a função LIKE recebe são os seguintes:

  • A expressão que está sendo avaliada.

  • O padrão de comparação

  • Se estiver presente, o caractere de escape, que é um parâmetro opcional.

Auxiliar Select

Gera uma função chamada SELECT quando um valor escalar precisa ser definido em uma variável

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

Nesse caso, ele gerará o seguinte código com o auxiliar SELECT

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

O auxiliar SELECT também pode ser usado para inserir em um valor local um valor recuperado de uma consulta. O auxiliar foi projetado especificamente para suportar o mesmo comportamento do SQL Server SELECT @local_variable. O parâmetro args representa cada operação aplicada a todas as variáveis locais dentro do select. Consulte também SELECT @Variable. Por exemplo:

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

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

GO
Copy

Nesse caso, as atribuições de variáveis serão convertidas para lambdas JavaScript a fim de emular o comportamento do SQL Server.

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

Auxiliar RAISERROR

Esse auxiliar é gerado quando há usos de uma chamada RAISERROR no código-fonte. Exemplo:

 var RAISERROR = (message,severity,state) => {
    snowflake.execute({
      sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?)`,
      binds : [message,severity,state]
    });
    var msg = `Message: ${message}, Level: ${severity}, State: ${state}`;
    throw msg;
  };
Copy

O RAISERROR executa o UPDATE_ERROR_VARS_UDF para armazenar o valor da mensagem de erro, a gravidade e o estado como variáveis de ambiente, caso precisem ser usados ao chamar qualquer uma das funções integradas de ERROR. Por fim, a mensagem de erro é lançada com o mesmo formato do SQL Server.

Auxiliar de função Identity

Esse auxiliar é gerado sempre que a função Identity é usada em um Select Into dentro de um procedimento.

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

Os parâmetros desse auxiliar são os mesmos da função original. Ele foi criado para gerar uma sequência que imite o comportamento da função Identity no TSQL. As alterações no código original são as seguintes:

  • Uma chamada de método adicional para a função IdentityHelper usando os mesmos parâmetros encontrados no código-fonte.

  • E uma chamada para IDENTITY_UDF um design de função para obter o próximo valor na sequência.

    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`);
Copy

Assim como no TSQL, se nenhum parâmetro for fornecido, (1,1) serão os valores padrão.

Auxiliar de procedimento CALL

Esse auxiliar é gerado sempre que há uma chamada para o que antes era uma função definida pelo usuário, mas que agora é um procedimento como resultado do processo de conversão.

    var CALL = (sql,binds = [],...args) => {
      EXEC("CALL " + sql,binds);
      _ROWS.next();
      return (_ROWS.getColumnValue(1))[0];
   };
Copy

O objetivo desse auxiliar é encapsular a lógica necessária para chamar procedimentos como se fossem funções.

Lembre-se de que essa funcionalidade é limitada, pois os procedimentos não podem ser invocados em consultas como SELECT.

Exemplo de uso, supondo que FooSelfAssign(@PAR INT) tenha sido convertido para um procedimento:

 // Input code
DECLARE @VAR1 INT = FooSelfAssign(1);
DECLARE @VAR4 INT = FooSelfAssign(FooSelfAssign(FooSelfAssign(FooSelfAssign(4))));
Copy
 // Output code
let VAR1 = CALL(`FooSelfAssign(1)`)
let VAR4 = CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(4)`)])])]);
Copy

Observe que a conversão para VAR1 é muito simples, mas para VAR4, o CALL mais externo contém uma lista com o restante de CALLs, como vinculações.

Cada CALL sucessivo é convertido para uma vinculação, se estiver contido em outro CALL.

2. Variables

DECLARE @Variable

Código-fonte

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

Código convertido

    let PRODUCT_LIST = ` `;
   let VARIABLE1;
   let VARIABLE2;
Copy

DECLARE @Variable Table

Nesse caso, DECLARE é usado para declarar uma tabela de variáveis. Vejamos um exemplo.

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

Se executarmos esse código no Sql Server, obteremos o seguinte resultado

Agora, vamos ver a transformação no Snowflake

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

Observe que das linhas 61 a 67 estão os resultados dessas instruções dentro do procedimento.

A tabela de variáveis Declare é transformada em uma tabela temporária. Observe que no nome o caractere @ foi substituído por T_.

Se executarmos esse código no Snowflake, não obteremos nenhum resultado. Ele exibirá apenas null. Isso ocorre porque o último Select está agora no auxiliar EXEC. Então, como sabemos que a tabela está lá?

Como ela foi criada como uma tabela temporária dentro do procedimento em um EXEC, podemos fazer um Select para essa tabela fora do procedimento.

 Select * from PUBLIC.T_VariableNameTable;
Copy

Se executarmos essa instrução, obteremos o seguinte resultado

SET @Variable

Por enquanto, Set Variable é transformado de acordo com a expressão que está no lado direito.

Se a expressão tiver uma transformação, ela será transformada em seu equivalente no JavaScript.

Exemplo

 -- 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
    
Copy

Código convertido

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

Como você pode ver no exemplo, o valor da variável NOTSUPPORTED é comentado, pois ela não está sendo transformada por enquanto. Observe que isso significa que a transformação ainda não foi concluída.

Outros tipos de conjuntos são comentados, por exemplo, os seguintes

 -- 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
Copy

Código convertido

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*/
;
$$;
Copy

SELECT @Variable

Por enquanto, SELECT @variable está sendo transformado em um Select simples, removendo as atribuições de variáveis e mantendo as expressões no lado direito do operador. As operações de atribuição das variáveis locais no Select serão substituídas por funções arrow que representam o mesmo comportamento da operação realizada durante a atribuição da variável local no SQL Server.

Entrada
 -- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1 AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
SELECT @VAR1 = COL1 + COL2, @VAR2 = COL3 FROM TABLE1;
GO
Copy
Saída
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.

let VAR1;
let VAR2;
SELECT(`   COL1 + COL2,
   COL3
   FROM
   TABLE1`,[],(value) => VAR1 = value,(value) => VAR2 = value);
$$;
Copy

3. Statements translation

SELECT

Forma básica

A forma básica de SELECT não tem vínculos, portanto, a conversão implica a criação de uma chamada para a função auxiliar EXEC, com um parâmetro:

 -- Source code:
SELECT * FROM DEMO_TABLE_1;
Copy
 // Translated code:
EXEC(`SELECT * FROM DEMO_TABLE_1`);
Copy

IF

Código-fonte

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

Código convertido

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

WHILE

Código-fonte

 WHILE ( Conditional_Expression )
BEGIN
   -- SQL STATEMENTS
END;
Copy

Código convertido

 while ( Conditional_Expression )
{
  // SQL STATEMENTS
}
Copy

EXEC / EXECUTE

Código-fonte

 -- 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
Copy

Código convertido

 -- 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]);
Copy

THROW

A transformação para THROW garante que o bloco de captura que recebe o erro tenha acesso às informações especificadas na instrução original.

Por exemplo:

 -- Case 1
THROW

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

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

Será transformado em:

 // Case 1
throw {};

// Case 2
throw { code: 123, message: "The error message", status: 1 };

// Case 3
throw { code: VAR1, message: VAR2, status: VAR3 };
Copy

RAISERROR

A função SQL Server RAISERROR não é compatível com o Snowflake. O SnowConvert identifica todos os usos para gerar um auxiliar que emula o comportamento original. Exemplo:

From
 -- Additional Params: -t JavaScript
CREATE OR ALTER PROCEDURE  RAISERRORTEST AS
BEGIN
    DECLARE @MessageTXT VARCHAR = 'ERROR MESSAGE';
    RAISERROR (N'E_INVALIDARG', 16, 1);
    RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
    RAISERROR(@MessageTXT, 16, 1);
END
GO
Copy
Para
CREATE OR REPLACE PROCEDURE RAISERRORTEST ()
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 MESSAGETXT = `ERROR MESSAGE`;
    RAISERROR("E_INVALIDARG","16","1");
    RAISERROR("Diagram does not exist or you do not have permission.","16","1");
    RAISERROR(MESSAGETXT,"16","1");
$$;
Copy

BREAK/CONTINUE

A transformação Break/Continue garante que o fluxo do código seja interrompido ou continue com outro bloco.

Por exemplo:

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

Será transformado em:

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

INSERT INTO EXEC

O código foi ligeiramente modificado devido ao fato de a instrução INSERT INTO [Table] EXEC(...) não ser compatível com o Snowflake, o que nos permite replicar o comportamento adicionando algumas linhas de código:

  • A primeira linha adicionada é uma chamada para insertIntoTemporaryTable para onde o código extraído do argumento dentro de EXEC; isso inserirá o conjunto de resultados em uma tabela temporária. Para obter mais informações sobre a função, consulte a seção Auxiliar Insert Into EXEC.

  • O Exec do Insert é removido do código e uma consulta recupera os resultados do EXEC da tabela temporária.

     SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable
    
Copy
  • A última linha adicionada é uma instrução DROP TABLE para a tabela temporária adicionada.

     DROP TABLE SnowConvertPivotTemporaryTable
    
Copy

Código-fonte:

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

INSERT INTO #Table1
EXEC (@DBTables);
Copy

Código convertido:

   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`)
Copy

BEGIN TRANSACTION

BEGIN TRANSACTION é transformado no comando BEGIN do Snowflake e inserido em uma chamada do auxiliar EXEC.

O auxiliar é responsável por executar de fato o resultado BEGIN.

Exemplo:

 -- Input code
BEGIN TRAN @transaction_name;
Copy
 // Output code
EXEC(`BEGIN`, []);
Copy

COMMIT TRANSACTION

COMMIT TRANSACTION é transformado no comando COMMIT do Snowflake e inserido em uma chamada do auxiliar EXEC.

O auxiliar é responsável por executar de fato o resultado COMMIT.

Exemplo:

 -- Input code
COMMIT TRAN @transaction_name;
Copy
 // Output code
EXEC(`COMMIT`, []);
Copy

ROLLBACK TRANSACTION

ROLLBACK TRANSACTION é transformado no comando ROLLBACK do Snowflake e inserido em uma chamada do auxiliar EXEC.

O auxiliar é responsável por executar de fato o resultado ROLLBACK.

Exemplo:

 -- Input code
ROLLBACK TRAN @transaction_name;
Copy
 // Output code
EXEC(`ROLLBACK`, []);
Copy

WAITFOR DELAY

A cláusula WAITFOR DELAY é transformada na função SYSTEM$WAIT do Snowflake. O parâmetro time_to_pass do DELAY é transformado em segundos, para uso como parâmetro na função SYSTEM$WAIT.

As outras variantes da cláusula WAITFOR não são compatíveis com o Snowflake e, portanto, são marcadas com a mensagem correspondente.

Exemplo:

 -- 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;
Copy
 // 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*/
   ;
Copy

3. Cursors

Como CURSORS não é compatível com o Snowflake, o SnowConvert mapeia sua funcionalidade para um auxiliar JavaScript que emula o comportamento original na plataforma de destino. Exemplo:

Entrada:

 -- 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
Copy
Saída:
CREATE OR REPLACE PROCEDURE procCursorHelper ()
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.

    var VENDOR_CURSOR = new CURSOR(`SELECT
       VendorID,
       Name
    FROM
       Purchasing.Vendor
    WHERE
       PreferredVendorStatus = 1
    ORDER BY VendorID`,[],false);
$$;
Copy

DECLARE CURSOR

Por enquanto, Declare Cursor está apenas sendo comentado.

Código-fonte

 DECLARE myCursor1 CURSOR FOR SELECT COL1 FROM TABLE1
Copy

Código convertido

 let myCursor1 = new CURSOR(`SELECT COL1 FROM TABLE1`,() => []);
Copy

OPEN

Código-fonte

 OPEN myCursor1
OPEN GLOBAL myCursor2
Copy

Código convertido

 myCursor1.OPEN();
myCursor2.OPEN()
Copy

FETCH

Código-fonte

 DECLARE @VALUE1 INT
FETCH NEXT FROM myCursor1 into @VALUE1
Copy

Código convertido

 var VALUE1;
myCursor1.FETCH_NEXT();
VALUE1 = myCursor1.INTO();
Copy

CLOSE

Código-fonte

 CLOSE myCursor1
CLOSE GLOBAL myCursor2
Copy

Código convertido

 myCursor1.CLOSE()
myCursor2.CLOSE()
Copy

DEALLOCATE

Código-fonte

 DEALLOCATE myCursor1
DEALLOCATE GLOBAL myCursor2
Copy

Código convertido

 myCursor1.DEALLOCATE()
myCursor2.DEALLOCATE()
Copy

@@FETCH_STATUS

Código-fonte

 @@FETCH_STATUS
Copy

Código convertido

 myCursor1.FETCH_STATUS()
Copy

@@CURSOR_ROWS

Código-fonte

 @@CURSOR_ROWS
Copy

Código convertido

 myCursor1.FETCH_STATUS()
Copy

4. Expressions

Operações binárias

Código-fonte

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

Código convertido

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

Condicionais

Código-fonte

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

Código convertido

 VAR1 > 0
VAR1 = 0
VAR1 < 0
VAR1 != 0
Copy
Predicado IN
Predicado NULL

Código-fonte

 @var1 is null
@var2 is not null
Copy

Código convertido

 VAR1 == null
VAR2 != null
Copy

5. Labels and Goto

Rótulos não têm o mesmo comportamento no JavaScript que o SQL Server tem. Para simular o comportamento, eles estão sendo transformados em funções. Seu uso está sendo substituído por uma chamada da função gerada que contém toda a lógica do rótulo. Exemplo:

Código-fonte

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

ERROR_EXIT:
	RETURN @ErrorStatus
Copy
Código convertido
 CREATE OR REPLACE PROCEDURE procWithLabels ()
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.

	SUCCESS_EXIT();
	ERROR_EXIT();
	function SUCCESS_EXIT() {
		ERRORSTATUS = 0;
		return ERRORSTATUS;
	}
	function ERROR_EXIT() {
		return ERRORSTATUS;
	}
$$;
Copy

Como você vê no exemplo acima, as declarações de função que eram os rótulos no código-fonte serão colocadas no final do código para torná-lo mais limpo.

GOTO é outro comando que não existe no JavaScript. Para simular seu comportamento, seus usos estão sendo transformados em chamadas para a função (rótulo) que é referenciada, precedida por uma instrução de retorno. Exemplo:

Código-fonte
 -- Additional Params: -t JavaScript
CREATE PROCEDURE [procWithLabels] 
AS
DECLARE @ErrorStatus int = 0;
IF @ErrorStatus <> 0 GOTO ERROR_EXIT
	
SUCCESS_EXIT:
	SET @ErrorStatus = 0
	RETURN @ErrorStatus

ERROR_EXIT:
	RETURN @ErrorStatus
Copy
Código convertido
CREATE OR REPLACE PROCEDURE procWithLabels ()
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 ERRORSTATUS = 0;
	if (ERRORSTATUS != 0) {
		return ERROR_EXIT();
	}
	SUCCESS_EXIT();
	ERROR_EXIT();
	function SUCCESS_EXIT() {
		ERRORSTATUS = 0;
		return ERRORSTATUS;
	}
	function ERROR_EXIT() {
		return ERRORSTATUS;
	}
$$;
Copy

Como você vê no exemplo acima, return é adicionado à chamada da função para interromper o fluxo do código, como o SQL Server com GOTO.

Problemas conhecidos

Não foram encontrados problemas.

EWIs relacionados

  1. SSC-EWI-0040: Instrução não suportada.

  2. SSC-EWI-0073: Revisão de equivalência funcional pendente.

Tabelas

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Create Table básico

Fonte

 CREATE TABLE [MYSCHEMA].[MYTABLE]
(
    [COL1] INT IDENTITY (1,1) NOT NULL,
    [COL2] INT,
    [COL2 COL3 COL4] VARCHAR,
    [COL VARCHAR_SPANISH] [VARCHAR](20) COLLATE Modern_Spanish_CI_AI DEFAULT 'HOLA',
    [COL VARCHAR_LATIN] [VARCHAR](20) COLLATE Latin1_General_CI_AI DEFAULT 'HELLO' 
);
Copy

Esperado

 CREATE OR REPLACE SEQUENCE MYSCHEMA.MYTABLE_COL1
    START WITH 1
    INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN MYSCHEMA.MYTABLE.COL1';

CREATE OR REPLACE TABLE MYSCHEMA.MYTABLE (
    COL1 INT DEFAULT MYSCHEMA.MYTABLE_COL1.NEXTVAL NOT NULL,
    COL2 INT,
    "COL2 COL3 COL4" VARCHAR,
    "COL VARCHAR_SPANISH" VARCHAR(20) COLLATE 'ES-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HOLA',
    "COL VARCHAR_LATIN" VARCHAR(20) COLLATE 'EN-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HELLO'
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
Copy

Tabelas temporárias

No código-fonte, pode haver alguns nomes de tabelas que começam com o caractere #.

 CREATE TABLE #MyLocalTempTable (
        COL1 INT,
        COL2 INT
);
Copy

Se esse for o caso, eles são transformados em tabelas temporárias no código de saída.

Vejamos como o código acima seria migrado.

 CREATE OR REPLACE TEMPORARY TABLE T_MyLocalTempTable (
        COL1 INT,
        COL2 INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Como você pode ver, TEMPORARY foi adicionado à definição da tabela e o caractere # foi substituído por T_.

Além disso, todas as referências da tabela também serão transformadas, para corresponder ao novo nome dado à tabela temporária.

Opção de colunas NULL e NOT NULL

NULL e NOT NULL são compatíveis com o Snowflake.

Fonte

 CREATE TABLE [SCHEMA1].[TABLE1](
	[COL1] [varchar](20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [SCHEMA1].[TABLE2](
	[COL1] [varchar](20) NULL
) ON [PRIMARY]
GO
Copy

Esperado

 CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
	COL1 VARCHAR(20) NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

CREATE OR REPLACE TABLE SCHEMA1.TABLE2 (
	COL1 VARCHAR(20) NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Opção de coluna Identity

Para colunas de identidade, uma sequência é criada e atribuída à coluna.

Fonte

 CREATE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT IDENTITY (666, 313) NOT NULL
);
Copy

Esperado

 CREATE OR REPLACE SEQUENCE acct3.UnidentifiedCash3_UnidentifiedCash_ID3
START WITH 666
INCREMENT BY 313
COMMENT = 'FOR TABLE-COLUMN acct3.UnidentifiedCash3.UnidentifiedCash_ID3';

CREATE OR REPLACE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT DEFAULT acct3.UnidentifiedCash3_UnidentifiedCash_ID3.NEXTVAL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Opção de coluna padrão

Expr padrão é suportado no Snowflake, mas no Sql Server ele pode vir junto com uma restrição Name. Como essa parte não é compatível com o Snowflake, ela foi removida e um aviso foi adicionado.

Fonte

 CREATE TABLE [SCHEMA1].[TABLE1] (
    [COL1] VARCHAR (10) CONSTRAINT [constraintName] DEFAULT ('0') NOT NULL
);
Copy

Esperado

 CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
    COL1 VARCHAR(10)
                     --** SSC-FDM-0012 - CONSTRAINT IN DEFAULT EXPRESSION IS NOT SUPPORTED IN SNOWFLAKE **
                     DEFAULT ('0') NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Restrição de coluna

Fonte

 CREATE TABLE [SalesLT].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[AddressLine1] [nvarchar](60) NOT NULL,
	[AddressLine2] [nvarchar](60) NULL,
	[City] [nvarchar](30) NOT NULL,
	[StateProvince] [dbo].[Name] NOT NULL,
	[CountryRegion] [dbo].[Name] NOT NULL,
	[PostalCode] [nvarchar](15) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
	(
		[AddressID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
	CONSTRAINT [AK_Address_rowguid] UNIQUE NONCLUSTERED 
	(
		[rowguid] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Copy

Esperado

 CREATE OR REPLACE SEQUENCE SalesLT.Address_AddressID
	START WITH 1
	INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN SalesLT.Address.AddressID';

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "[dbo].[Name]" **
CREATE OR REPLACE TABLE SalesLT.Address (
	AddressID INT DEFAULT SalesLT.Address_AddressID.NEXTVAL NOT NULL,
	AddressLine1 VARCHAR(60) NOT NULL,
	AddressLine2 VARCHAR(60) NULL,
	City VARCHAR(30) NOT NULL,
	StateProvince VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
	CountryRegion VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
	PostalCode VARCHAR(15) NOT NULL,
	rowguid VARCHAR
 	               !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
 	               ROWGUIDCOL  NOT NULL,
	ModifiedDate TIMESTAMP_NTZ(3) NOT NULL,
	CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID),
	CONSTRAINT AK_Address_rowguid UNIQUE (rowguid)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
;
Copy

Opção de coluna Collate

Para obter a transformação Collate, consulte o link a seguir.

collate.md

Opção de coluna ENCRYPTED WITH

Encrypted With não é compatível com o Snowflake, portanto está sendo removido e um aviso foi adicionado.

Fonte

 CREATE TABLE [SCHEMA1].[TABLE1] (
    [COL1] NVARCHAR(60) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);
Copy

Esperado

 CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
    COL1 VARCHAR(60)
--                     --** SSC-FDM-TS0009 - ENCRYPTED WITH NOT SUPPORTED IN SNOWFLAKE **
--                     ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

NOT FOR REPLICATION

A opção NOT FOR REPLICATION não é compatível com o Snowflake. Ela é usado para a identidade que está sendo migrada para um SEQUENCE.

Aviso

Observe que NOT FOR REPLICATION é uma instrução que não é necessária no Snowflake porque é convertida para um equivalente, portanto, é removida.

Fonte

 CREATE TABLE [TABLE1] (
    [COL1] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
) ON [PRIMARY];
Copy

Saída

 CREATE OR REPLACE SEQUENCE TABLE1_COL1
    START WITH 1
    INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN TABLE1.COL1';

CREATE OR REPLACE TABLE TABLE1 (
    COL1 INT DEFAULT TABLE1_COL1.NEXTVAL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

On Primary

A opção ON PRIMARY é uma instrução usada no SQL Server para definir em qual arquivo um objeto, por exemplo, uma tabela, será criado. Por exemplo, em um grupo de arquivos primário ou secundário dentro do banco de dados. O Snowflake oferece uma lógica diferente e indica restrições distintas. Para obter mais informações, consulte a documentação do Snowflake a seguir.

Fonte

 CREATE TABLE [TABLE1](
[COL1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
 CONSTRAINT [pk_dimAddress_AddressId] PRIMARY KEY CLUSTERED ([COL1]) 
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Copy
Saída
 CREATE OR REPLACE TABLE TABLE1 (
 COL1 VARCHAR(255) COLLATE 'EN-CI-AS' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ /*** SSC-FDM-TS0002 - COLLATION FOR VALUE CP1 NOT SUPPORTED ***/ NOT NULL
  CONSTRAINT pk_dimAddress_AddressId PRIMARY KEY (COL1)
 )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
 ;
Copy

Classificação de colunas ASC/DESC

A classificação de colunas não é compatível com o Snowflake; as palavras-chave ASC ou DESC estão sendo removidas.

Fonte

 CREATE TABLE [TABLE1](
	[COL1] [int] NOT NULL,
 CONSTRAINT [constraint1] PRIMARY KEY CLUSTERED ([COL1] ASC)
) ON [PRIMARY]
Copy
Saída
 CREATE OR REPLACE TABLE TABLE1 (
	COL1 INT NOT NULL,
	 CONSTRAINT constraint1 PRIMARY KEY (COL1)
	)
	COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
	;
Copy

Colunas computadas

As colunas computadas são compatíveis com o Snowflake, só precisamos adicionar o tipo de dados explícito para podermos implantar a tabela, por exemplo.

Fonte

 CREATE TABLE [TABLE1](
	[COL2] [int] NOT NULL,
	[COL2] [int] NOT NULL,
	[COL1] AS (COL3 * COL2),
)
Copy
Saída
 CREATE OR REPLACE TABLE TABLE1 (
	COL2 INT NOT NULL,
	COL2 INT NOT NULL,
	COL1 VARIANT AS (COL3 * COL2) /*** 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"}}'
;
Copy

Se a expressão computada não puder ser transformada, será adicionado um aviso e, em vez disso, será usada uma definição de coluna simples com o tipo de retorno da expressão, como no exemplo a seguir:

Fonte
 CREATE TABLE [TABLE1](
	[Col1] AS (CONVERT ([XML], ExpressionValue))
)
Copy

A expressão CONVERT ([NUMERIC], ExpressionValue) ainda não é compatível com o SnowConvert, portanto, depois de ser inspecionada, o SnowConvert determinará que seu tipo é XML, de modo que a transformação será

 CREATE OR REPLACE TABLE TABLE1 (
	Col1 TEXT AS (CAST(ExpressionValue AS VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XML DATA TYPE CONVERTED TO VARIANT ***/!!!)) /*** 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"}}'
;
Copy

O SnowConvert executará um processo para determinar o tipo de expressão original no SQL Server. No entanto, a coluna terá o tipo de destino equivalente. No exemplo anterior, o tipo de coluna no SQLServer era XML, mas o tipo de destino no Snowflake para armazenar um XML é TEXT. Para obter mais informações sobre o mapeamento de tipos de dados, consulte as seções de tipos de dados.

Opção de coluna MASKED WITH

No SQL Server, o mascaramento de dados é usado para manter as informações confidenciais longe de usuários não privilegiados. Consulte a documentação do SQL SERVER para obter mais informações. No Snowflake, há uma funcionalidade dinâmica de mascaramento de dados, mas ela está disponível apenas para a Enterprise Edition. Consulte a seguinte documentação do Snowflake.

Entrada

 CREATE TABLE TABLE1
(
	[COL1] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NULL
);
Copy
Saída
 CREATE OR REPLACE TABLE TABLE1
(
	COL1 VARCHAR(50)
 	                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'default()') NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Opção de coluna ROWGUIDCOL

ROWGUIDCOL não é mais aplicável no Snowflake. Ele é usado no SQL Server para os tipos UNIQUEIDENTIFIER que atualmente são convertidos para VARCHAR. Por exemplo:

Entrada

 CREATE TABLE TABLEROWID (
    [ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];
Copy
Saída
 CREATE OR REPLACE TABLE TABLEROWID (
    ROWGUID VARCHAR
                    !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                    ROWGUIDCOL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Opção de coluna GENERATEDALWAYSASROWSTART/END

ROW START/END não é compatível com o Snowflake. Um erro é adicionado quando o SnowConvert tenta transformar esse tipo de opção de coluna.

Entrada

 CREATE TABLE TABLEROWID (
    [COL1] DATETIME GENERATED ALWAYS AS ROW START NOT NULL
) ON [PRIMARY];
Copy
Saída
 CREATE OR REPLACE TABLE TABLEROWID (
    COL1 TIMESTAMP_NTZ(3) GENERATED ALWAYS AS ROW START !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GeneratedClause' NODE ***/!!! NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Problemas conhecidos

Não foram encontrados problemas.

EWIs relacionados

  1. SSC-EWI-0036: Tipo de dados convertido em outro tipo de dados.

  2. SSC-EWI-0040: Instrução não suportada.

  3. SSC-EWI-0073: Revisão de equivalência funcional pendente.

  4. SSC-EWI-TS0017: Não há suporte para mascaramento.

  5. SSC-FDM-0012: A restrição na expressão padrão não é compatível.

  6. SSC-FDM-TS0002: Essa mensagem é exibida quando há uma cláusula collate que não é compatível com o Snowflake.

  7. SSC-FDM-TS0009: Encrypted with não suportado no Snowflake.

  8. SSC-FDM-TS0014: Coluna computada transformada.

  9. SSC-FDM-TS0015: O tipo de dados não é compatível com o Snowflake.

  10. SSC-PRF-0002: As colunas que não diferenciam maiúsculas de minúsculas podem diminuir o desempenho das consultas.

Exibições

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Nesta seção, verificaremos a transformação para Create View.

Amostra de padrões da origem

SIMPLE CREATE VIEW

O exemplo a seguir mostra uma transformação para uma instrução CREATE VIEW simples.

 CREATE VIEW VIEWNAME
AS
SELECT AValue from ATable;
Copy

Esperado

 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE OR ALTER VIEW

A definição CREATE OR ALTER usada no SqlServer é transformada em CREATE OR REPLACE no Snowflake.

 CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable;
Copy
 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE VIEW WITH

Nesse tipo de visualização, após o nome da visualização, podem vir as seguintes cláusulas

  • WITH ENCRYPTION

  • WITH SCHEMABINDING

  • WITH VIEW_METADATA

Aviso

Observe que as cláusulas acima foram removidas da conversão, pois não são relevantes na sintaxe do Snowflake.

 CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION  
AS
SELECT AValue from ATable;
Copy
 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE VIEW AS SELECT WITH CHECK OPTION

Nesse tipo de visualização, a cláusula WITH CHECK OPTION vem depois do final da instrução Select usada em Create View.

Aviso

Observe que WITH CHECK OPTIONfoi removido da conversão, pois não é relevante na sintaxe do Snowflake.

 CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable
WITH CHECK OPTION;
Copy

Esperado

 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE VIEW AS COMMON TABLE EXPRESSION

As expressões de tabela comuns devem ser usadas para recuperar os dados:

 CREATE VIEW EMPLOYEEIDVIEW
AS
WITH CTE AS ( SELECT NationalIDNumber from [HumanResources].[Employee] 
UNION ALL 
SELECT BusinessEntityID FROM [HumanResources].[EmployeeDepartmentHistory] )
SELECT * FROM MyCTE;
Copy
 CREATE OR REPLACE VIEW EMPLOYEEIDVIEW
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
NationalIDNumber
from
HumanResources.Employee
UNION ALL
SELECT
BusinessEntityID
FROM
HumanResources.EmployeeDepartmentHistory
)
SELECT
*
FROM
MyCTE;
Copy
UNSUPPORTED SCENARIOS

As expressões de tabela comuns com instruções Update, Insert ou Delete serão comentadas porque não são compatíveis com o Snowflake e o SQLServer.

No caso de um CTE inválido ser adicionado à visualização, ele será completamente comentado.

 --!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - COMMON TABLE EXPRESSION IN VIEW NOT SUPPORTED ***/!!!
--CREATE OR REPLACE VIEW PUBLIC.EmployeeInsertVew
--AS
--WITH MyCTE AS ( SELECT
--NationalIDNumber
--from
--HumanResources.Employee
--UNION ALL
--SELECT
--BusinessEntityID
--FROM
--HumanResources.EmployeeDepartmentHistory
--)
--INSERT INTO PUBLIC.Dummy
Copy

FINAL SAMPLE

Vamos ver um exemplo final, vamos juntar todos os casos que vimos até agora e ver como seria a transformação

 CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION  
AS  
Select AValue from ATable
WITH CHECK OPTION;  
Copy

Esperado

 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
Select
AValue
from
ATable;
Copy

Como você pode ver, alteramos OR ALTER por OR REPLACE e removemos a cláusula WITH ENCRYPTION que vem depois do nome da visualização e WITH CHECK OPTION que vem depois do Select.

EWIs relacionados

  1. SSC-PRF-TS0001: Aviso de desempenho - recursão para CTE não verificada. Pode exigir uma palavra-chave recursiva.

Azure Synapse Analytics

Applies to
  • [x] Azure Synapse Analytics

Descrição

Esta seção apresenta a conversão da sintaxe específica das tabelas do Azure Synapse Analytics.

Nota

Para a maioria dos padrões, verifique a especificação de conversão para tabelas gerais, pois eles compartilham praticamente a mesma sintaxe e o mesmo comportamento.

Create Table

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( 
      { column_name &#x3C;data_type>  [ &#x3C;column_options> ] } [ ,...n ]
    )  
    [ WITH ( &#x3C;table_option> [ ,...n ] ) ]  
[;]
Copy

Create Table As

 CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>  
    OPTION <query_hint> 
[;]  
Copy

Amostra de padrões da origem

Opções de tabela WITH

O Azure Synapse Analytics apresenta uma sintaxe adicional para definir opções de tabela.

 <table_option> ::=
    {
       CLUSTERED COLUMNSTORE INDEX -- default for Azure Synapse Analytics 
      | CLUSTERED COLUMNSTORE INDEX ORDER (column [,...n])  
      | HEAP --default for Parallel Data Warehouse
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) -- default is ASC
    }  
    {
        DISTRIBUTION = HASH ( distribution_column_name )
      | DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 
      | DISTRIBUTION = ROUND_ROBIN -- default for Azure Synapse Analytics
      | DISTRIBUTION = REPLICATE -- default for Parallel Data Warehouse
    }
    | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] -- default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) )
Copy

O Snowflake lida automaticamente com a otimização de tabelas por meio de mecanismos como o microparticionamento. Por esse motivo, não existe uma sintaxe equivalente para algumas dessas opções de tabela no Snowflake. Portanto, não é necessário definir algumas das opções de tabela do Transact.

Opções de tabela que serão omitidas:

  • CLUSTERED COLUMNSTORE INDEX (sem coluna)

  • HEAP

  • DISTRIBUTION

  • PARTITION

CLUSTERED [ COLUMNSTORE ] INDEX com colunas será transformado em CLUSTER BY do Snowflake. Uma avaliação de desempenho PRF será adicionada, pois é aconselhável verificar se é necessário definir um CLUSTER KEY.

Transact
 CREATE TABLE my_table (
    enterprise_cif INT,
    name NVARCHAR(100),
    address NVARCHAR(255),
    created_at DATETIME
) 
WITH (
    DISTRIBUTION = HASH(enterprise_cif),
    CLUSTERED INDEX (enterprise_cif)
);
Copy
Snowflake
 CREATE OR REPLACE TABLE my_table (
  enterprise_cif INT,
  name VARCHAR(100),
  address VARCHAR(255),
  created_at TIMESTAMP_NTZ(3)
)
--** SSC-PRF-0007 - PERFORMANCE REVIEW - CLUSTER BY **
CLUSTER BY (enterprise_cif)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/09/2024" }}'
;
Copy

Problemas conhecidos

Não foram encontrados problemas.

EWIs relacionados

  1. [SSC-PRF-0007](../../general/technical-documentation/issues-and-troubleshooting/performance-review-messages/README. md#ssc-prf-0007): PERFORMANCE REVIEW - CLUSTER BY.

TEXTIMAGE_ON

Applies to
  • [x] SQL Server

Nota

Instrução não relevante.

Aviso

Observe que essa instrução é removida da migração porque é uma sintaxe não relevante. Isso significa que não é necessária no Snowflake.

Descrição

TEXTIMAGE_ON [PRIMARY] é uma forma de o Transact lidar com os grandes grupos de informações dentro de uma tabela. No Snowflake, não é necessário definir esses tipos de características porque o Snowflake lida com grandes arquivos de dados ou informações em um arranjo diferente.

Amostra de padrões da origem

Observe que, neste exemplo, TEXTIMAGE_ON [PRIMARY] foi removido devido à sintaxe desnecessária.

 CREATE TABLE [dbo].[TEST_Person](
	[date_updated] [datetime] NULL
 ) TEXTIMAGE_ON [PRIMARY]
Copy
 CREATE OR REPLACE TABLE dbo.TEST_Person (
	date_updated TIMESTAMP_NTZ(3) NULL
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
 ;
Copy

Problemas conhecidos

Não foram encontrados problemas.

EWIs Relacionados

Sem EWIs relacionados.

FUNCTION

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Descrição

O SQL Server suporta apenas dois tipos de Funções Definidas pelo Usuário:

Usando esses tipos de UDFs, é possível subcategorizá-los em simples e complexos, de acordo com a lógica interna.

UDFs Simples combinam a sintaxe do SQL Server com a sintaxe do Snowflake. Esse tipo não acrescenta nenhuma lógica e vai direto ao resultado. Geralmente, eles correspondem aos SQL UDFs do Snowflake.\ \ UDFs complexos fazem uso extensivo de instruções específicas (INSERT, DELETE, UPDATE, SET, DECLARE, etc.) ou blocos de controle de fluxo (IF…ELSE, WHILE etc.) e geralmente representa uma incompatibilidade ou violação da definição de SQL UDFs do Snowflake.

Limitações

Transact UDFs têm algumas limitações que não estão presentes em outros mecanismos de banco de dados (como Oracle e Teradata). Essas limitações ajudam as conversões ao restringirem o escopo da falha. Isso significa que há cenários específicos que podemos esperar evitar.

Aqui estão algumas das limitações que o SQL Server tem sobre UDFs

  • UDFs não podem ser usados para executar ações que modifiquem o estado do banco de dados

  • As funções definidas pelo usuário não podem conter uma cláusula OUTPUT INTO que tenha uma tabela como destino

  • As funções definidas pelo usuário não podem retornar vários conjuntos de resultados. Use um procedimento armazenado se você precisar retornar vários conjuntos de resultados.

Para obter a lista completa, consulte este link Criar funções definidas pelo usuário (mecanismo de banco de dados)

scalar.md

inline-table-valued.md

INLINE TABLE-VALUED

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Descrição

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

As funções Inline Table-Valued são expressões de tabela que podem aceitar parâmetros, executar uma instrução SELECT e retornar um TABLE (Referência da linguagem SQL Server - Criar uma função Inline Table-Valued).

Sintaxe do Transact

 -- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Copy

Sintaxe do 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>'sql
Copy

Amostra de padrões da origem

A seção a seguir descreve todos os possíveis padrões de código-fonte que podem aparecer nesse tipo de sintaxe CREATE FUNCTION.

Para funções Inline Table-Valued, só pode haver uma instrução por corpo, que pode ser:

  • Instrução SELECT

  • Expressão de tabela comum WITH

Selecionar e retornar valores diretamente de uma tabela

Este é o cenário mais simples, executando uma seleção simples de uma tabela e retornando esses valores

Transact-SQL
 CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
);

GO

SELECT * from GetDepartmentInfo()
Copy
DepartmentID|Name                      |GroupName                           |
------------+--------------------------+------------------------------------+
           1|Engineering               |Research and Development            |
           2|Tool Design               |Research and Development            |
           3|Sales                     |Sales and Marketing                 |
           4|Marketing                 |Sales and Marketing                 |
           5|Purchasing                |Inventory Management                |
           6|Research and Development  |Research and Development            |
           7|Production                |Manufacturing                       |
           8|Production Control        |Manufacturing                       |
           9|Human Resources           |Executive General and Administration|
          10|Finance                   |Executive General and Administration|
          11|Information Services      |Executive General and Administration|
          12|Document Control          |Quality Assurance                   |
          13|Quality Assurance         |Quality Assurance                   |
          14|Facilities and Maintenance|Executive General and Administration|
          15|Shipping and Receiving    |Inventory Management                |
          16|Executive                 |Executive General and Administration|

Copy
Snowflake SQL
 CREATE OR REPLACE FUNCTION GetDepartmentInfo ()
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
$$
    SELECT
    CAST(DepartmentID AS STRING),
    CAST(Name AS STRING),
    CAST(GroupName AS STRING)
    FROM
    HumanResources.Department
$$;

SELECT
    *
from
    TABLE(GetDepartmentInfo());
Copy
DepartmentID|Name                      |GroupName                           |
------------+--------------------------+------------------------------------+
           1|Engineering               |Research and Development            |
           2|Tool Design               |Research and Development            |
           3|Sales                     |Sales and Marketing                 |
           4|Marketing                 |Sales and Marketing                 |
           5|Purchasing                |Inventory Management                |
           6|Research and Development  |Research and Development            |
           7|Production                |Manufacturing                       |
           8|Production Control        |Manufacturing                       |
           9|Human Resources           |Executive General and Administration|
          10|Finance                   |Executive General and Administration|
          11|Information Services      |Executive General and Administration|
          12|Document Control          |Quality Assurance                   |
          13|Quality Assurance         |Quality Assurance                   |
          14|Facilities and Maintenance|Executive General and Administration|
          15|Shipping and Receiving    |Inventory Management                |
          16|Executive                 |Executive General and Administration|

Copy

Selecionar e retornar valores de várias tabelas renomeando colunas e usando funções incorporadas

Este é um exemplo de uma consulta que usa funções internas em uma instrução Select, obtendo dados de diferentes tabelas, renomeando colunas e retornando uma tabela.

Transact-SQL
 CREATE FUNCTION GetPersonBasicInfo()
RETURNS TABLE
AS 
RETURN
(	
 SELECT TOP (20)
      P.PersonType,
      P.FirstName,
      E.JobTitle,
	  E.Gender,
      YEAR(E.HireDate) as HIREYEAR
  FROM 
      Person.Person P
  INNER JOIN 
      HumanResources.Employee E
  ON
      P.BusinessEntityID = E.BusinessEntityID 
);

GO

SELECT * FROM GetPersonBasicInfo();
Copy
PersonType|FirstName|JobTitle                         |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM        |Ken      |Chief Executive Officer          |M     |    2009|
EM        |Terri    |Vice President of Engineering    |F     |    2008|
EM        |Roberto  |Engineering Manager              |M     |    2007|
EM        |Rob      |Senior Tool Designer             |M     |    2007|
EM        |Gail     |Design Engineer                  |F     |    2008|
EM        |Jossef   |Design Engineer                  |M     |    2008|
EM        |Dylan    |Research and Development Manager |M     |    2009|
EM        |Diane    |Research and Development Engineer|F     |    2008|
EM        |Gigi     |Research and Development Engineer|F     |    2009|
EM        |Michael  |Research and Development Manager |M     |    2009|
EM        |Ovidiu   |Senior Tool Designer             |M     |    2010|
EM        |Thierry  |Tool Designer                    |M     |    2007|
EM        |Janice   |Tool Designer                    |F     |    2010|
EM        |Michael  |Senior Design Engineer           |M     |    2010|
EM        |Sharon   |Design Engineer                  |F     |    2011|
EM        |David    |Marketing Manager                |M     |    2007|
EM        |Kevin    |Marketing Assistant              |M     |    2007|
EM        |John     |Marketing Specialist             |M     |    2011|
EM        |Mary     |Marketing Assistant              |F     |    2011|
EM        |Wanida   |Marketing Assistant              |F     |    2011|    

Copy
Snowflake SQL
 CREATE OR REPLACE FUNCTION GetPersonBasicInfo ()
RETURNS TABLE(
	PersonType STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN PersonType WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	FirstName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN FirstName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	JobTitle STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN JobTitle WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	HIREYEAR INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
	 SELECT
	 TOP 20
		CAST(P.PersonType AS STRING),
		CAST(P.FirstName AS STRING),
		CAST(E.JobTitle AS STRING),
		CAST(E.Gender AS STRING),
		YEAR(E.HireDate :: TIMESTAMP) as HIREYEAR
	  FROM
		Person.Person P
	  INNER JOIN
			HumanResources.Employee E
	  ON P.BusinessEntityID = E.BusinessEntityID
$$;

SELECT
	 *
FROM
	 TABLE(GetPersonBasicInfo());
Copy
PersonType|FirstName|JobTitle                         |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM        |Ken      |Chief Executive Officer          |M     |    2009|
EM        |Terri    |Vice President of Engineering    |F     |    2008|
EM        |Roberto  |Engineering Manager              |M     |    2007|
EM        |Rob      |Senior Tool Designer             |M     |    2007|
EM        |Gail     |Design Engineer                  |F     |    2008|
EM        |Jossef   |Design Engineer                  |M     |    2008|
EM        |Dylan    |Research and Development Manager |M     |    2009|
EM        |Diane    |Research and Development Engineer|F     |    2008|
EM        |Gigi     |Research and Development Engineer|F     |    2009|
EM        |Michael  |Research and Development Manager |M     |    2009|
EM        |Ovidiu   |Senior Tool Designer             |M     |    2010|
EM        |Thierry  |Tool Designer                    |M     |    2007|
EM        |Janice   |Tool Designer                    |F     |    2010|
EM        |Michael  |Senior Design Engineer           |M     |    2010|
EM        |Sharon   |Design Engineer                  |F     |    2011|
EM        |David    |Marketing Manager                |M     |    2007|
EM        |Kevin    |Marketing Assistant              |M     |    2007|
EM        |John     |Marketing Specialist             |M     |    2011|
EM        |Mary     |Marketing Assistant              |F     |    2011|
EM        |Wanida   |Marketing Assistant              |F     |    2011|

Copy

Selecionar colunas usando a instrução WITH

O corpo de uma função com valor de tabela em linha também pode ser especificado usando uma instrução WITH, conforme mostrado abaixo.

Transact-SQL
 CREATE FUNCTION GetMaritalStatusByGender
(
	@P_Gender nchar(1)
)

RETURNS TABLE 
AS
RETURN
(
  WITH CTE AS 
	(
	 SELECT BusinessEntityID, MaritalStatus, Gender 
	 FROM HumanResources.Employee 
	 where Gender = @P_Gender
	)	
  SELECT 
	MaritalStatus, Gender, CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM 
	CTE INNER JOIN Person.Person P
  ON
	CTE.BusinessEntityID = P.BusinessEntityID
);

GO

select * from GetMaritalStatusByGender('F');
Copy
MaritalStatus|Gender|Name                   |
-------------+------+-----------------------+
S            |F     |Terri Duffy            |
M            |F     |Gail Erickson          |
S            |F     |Diane Margheim         |
M            |F     |Gigi Matthew           |
M            |F     |Janice Galvin          |
M            |F     |Sharon Salavaria       |
S            |F     |Mary Dempsey           |
M            |F     |Wanida Benshoof        |
M            |F     |Mary Gibson            |
M            |F     |Jill Williams          |
S            |F     |Jo Brown               |
M            |F     |Britta Simon           |
M            |F     |Margie Shoop           |
M            |F     |Rebecca Laszlo         |
M            |F     |Suchitra Mohan         |
M            |F     |Kim Abercrombie        |
S            |F     |JoLynn Dobney          |
M            |F     |Nancy Anderson         |
M            |F     |Ruth Ellerbrock        |
M            |F     |Doris Hartwig          |
M            |F     |Diane Glimp            |
M            |F     |Bonnie Kearney         |
M            |F     |Denise Smith           |
S            |F     |Diane Tibbott          |
M            |F     |Carole Poland          |
M            |F     |Carol Philips          |
M            |F     |Merav Netz             |
S            |F     |Betsy Stadick          |
S            |F     |Danielle Tiedt         |
S            |F     |Kimberly Zimmerman     |
M            |F     |Elizabeth Keyser       |
M            |F     |Mary Baker             |
M            |F     |Alice Ciccu            |
M            |F     |Linda Moschell         |
S            |F     |Angela Barbariol       |
S            |F     |Kitti Lertpiriyasuwat  |
S            |F     |Susan Eaton            |
S            |F     |Kim Ralls              |
M            |F     |Nicole Holliday        |
S            |F     |Anibal Sousa           |
M            |F     |Samantha Smith         |
S            |F     |Olinda Turner          |
S            |F     |Cynthia Randall        |
M            |F     |Sandra Reátegui Alayo  |
S            |F     |Linda Randall          |
S            |F     |Shelley Dyck           |
S            |F     |Laura Steele           |
S            |F     |Susan Metters          |
S            |F     |Katie McAskill-White   |
M            |F     |Barbara Decker         |
M            |F     |Yvonne McKay           |
S            |F     |Janeth Esteves         |
M            |F     |Brenda Diaz            |
M            |F     |Lorraine Nay           |
M            |F     |Paula Nartker          |
S            |F     |Lori Kane              |
M            |F     |Kathie Flood           |
S            |F     |Belinda Newman         |
M            |F     |Karen Berge            |
M            |F     |Lori Penor             |
M            |F     |Jo Berry               |
M            |F     |Laura Norman           |
M            |F     |Paula Barreto de Mattos|
M            |F     |Mindy Martin           |
M            |F     |Deborah Poe            |
S            |F     |Candy Spoon            |
M            |F     |Barbara Moreland       |
M            |F     |Janet Sheperdigian     |
S            |F     |Wendy Kahn             |
S            |F     |Sheela Word            |
M            |F     |Linda Meisner          |
S            |F     |Erin Hagens            |
M            |F     |Annette Hill           |
S            |F     |Jean Trenary           |
S            |F     |Stephanie Conroy       |
S            |F     |Karen Berg             |
M            |F     |Janaina Bueno          |
M            |F     |Linda Mitchell         |
S            |F     |Jillian Carson         |
S            |F     |Pamela Ansman-Wolfe    |
S            |F     |Lynn Tsoflias          |
M            |F     |Amy Alberts            |
S            |F     |Rachel Valdez          |
M            |F     |Jae Pak                |

Copy
Snowflake SQL
 CREATE OR REPLACE FUNCTION GetMaritalStatusByGender
(P_GENDER STRING
)
RETURNS TABLE(
	MaritalStatus STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN MaritalStatus WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Name VARCHAR
)
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
	 	BusinessEntityID,
	 	MaritalStatus,
	 	Gender
	 FROM
	 	HumanResources.Employee
	 where
	 	Gender = :P_GENDER
	)
  SELECT
	 CAST(MaritalStatus AS STRING),
	 CAST(Gender AS STRING),
	 CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM
	 CTE
	 INNER JOIN
	 	Person.Person P
  ON CTE.BusinessEntityID = P.BusinessEntityID
$$;

select
  *
from GetMaritalStatusByGender('F') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TableValuedFunctionCall' NODE ***/!!!;
Copy
MaritalStatus|Gender|Name                   |
-------------+------+-----------------------+
S            |F     |Terri Duffy            |
M            |F     |Gail Erickson          |
S            |F     |Diane Margheim         |
M            |F     |Gigi Matthew           |
M            |F     |Janice Galvin          |
M            |F     |Sharon Salavaria       |
S            |F     |Mary Dempsey           |
M            |F     |Wanida Benshoof        |
M            |F     |Mary Gibson            |
M            |F     |Jill Williams          |
S            |F     |Jo Brown               |
M            |F     |Britta Simon           |
M            |F     |Margie Shoop           |
M            |F     |Rebecca Laszlo         |
M            |F     |Suchitra Mohan         |
M            |F     |Kim Abercrombie        |
S            |F     |JoLynn Dobney          |
M            |F     |Nancy Anderson         |
M            |F     |Ruth Ellerbrock        |
M            |F     |Doris Hartwig          |
M            |F     |Diane Glimp            |
M            |F     |Bonnie Kearney         |
M            |F     |Denise Smith           |
S            |F     |Diane Tibbott          |
M            |F     |Carole Poland          |
M            |F     |Carol Philips          |
M            |F     |Merav Netz             |
S            |F     |Betsy Stadick          |
S            |F     |Danielle Tiedt         |
S            |F     |Kimberly Zimmerman     |
M            |F     |Elizabeth Keyser       |
M            |F     |Mary Baker             |
M            |F     |Alice Ciccu            |
M            |F     |Linda Moschell         |
S            |F     |Angela Barbariol       |
S            |F     |Kitti Lertpiriyasuwat  |
S            |F     |Susan Eaton            |
S            |F     |Kim Ralls              |
M            |F     |Nicole Holliday        |
S            |F     |Anibal Sousa           |
M            |F     |Samantha Smith         |
S            |F     |Olinda Turner          |
S            |F     |Cynthia Randall        |
M            |F     |Sandra Reátegui Alayo  |
S            |F     |Linda Randall          |
S            |F     |Shelley Dyck           |
S            |F     |Laura Steele           |
S            |F     |Susan Metters          |
S            |F     |Katie McAskill-White   |
M            |F     |Barbara Decker         |
M            |F     |Yvonne McKay           |
S            |F     |Janeth Esteves         |
M            |F     |Brenda Diaz            |
M            |F     |Lorraine Nay           |
M            |F     |Paula Nartker          |
S            |F     |Lori Kane              |
M            |F     |Kathie Flood           |
S            |F     |Belinda Newman         |
M            |F     |Karen Berge            |
M            |F     |Lori Penor             |
M            |F     |Jo Berry               |
M            |F     |Laura Norman           |
M            |F     |Paula Barreto de Mattos|
M            |F     |Mindy Martin           |
M            |F     |Deborah Poe            |
S            |F     |Candy Spoon            |
M            |F     |Barbara Moreland       |
M            |F     |Janet Sheperdigian     |
S            |F     |Wendy Kahn             |
S            |F     |Sheela Word            |
M            |F     |Linda Meisner          |
S            |F     |Erin Hagens            |
M            |F     |Annette Hill           |
S            |F     |Jean Trenary           |
S            |F     |Stephanie Conroy       |
S            |F     |Karen Berg             |
M            |F     |Janaina Bueno          |
M            |F     |Linda Mitchell         |
S            |F     |Jillian Carson         |
S            |F     |Pamela Ansman-Wolfe    |
S            |F     |Lynn Tsoflias          |
M            |F     |Amy Alberts            |
S            |F     |Rachel Valdez          |
M            |F     |Jae Pak                |

Copy

Problemas conhecidos

Não foram encontrados problemas

EWIs relacionados

  1. SSC-FDM-TS0012: Não foram encontradas informações para a expressão. CAST para STRING usado

  2. SSC-PRF-TS0001: Aviso de desempenho - recursão para CTE não verificada. Pode exigir uma palavra-chave recursiva.

  3. SSC-EWI-0073: Revisão de equivalência funcional pendente

MULTI-STATEMENT TABLE-VALUED

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Nota

Todos os exemplos de código nesta página ainda não foram implementados no SnowConvert. Eles devem ser interpretados como uma referência de como cada cenário deve ser convertido para o Snowflake. Essas conversões podem mudar no futuro. Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição

Multi-statement Table-Valued é semelhante a Inline-statement table-valued (INLINE TABLE-VALUED. No entanto, a função Multi-statement table-valued pode ter mais de uma instrução no corpo da função, as colunas da tabela são especificadas no tipo de retorno e ela tem um bloco BEGIN/END (Referência da linguagem SQL Server - Criar uma função Multi-statement table-valued

Sintaxe do Transact-SQL

 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
[ ; ]
Copy

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>'
Copy

Amostra de padrões da origem

A seção a seguir descreve todos os possíveis padrões de código-fonte que podem aparecer nesse tipo de sintaxe CREATE FUNCTION.

O corpo da função Multi-Statement Table-Valued deve ser uma instrução SELECT. Por esse motivo, as outras instruções devem ser chamadas separadamente.

Inserir valores em uma tabela

Insere uma ou mais linhas na tabela e retorna a tabela com os novos valores

Transact-SQL
 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();
Copy
BEHAVIORAL_SEGMENT|
------------------+
           Unknown|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION calc_behavioral_segment ()
RETURNS BEHAVIORAL_SEGMENTS TABLE (
	behavioral_segment VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
	DECLARE @col varchar(15)
	SET @col = 'Unknown'
	INSERT INTO @behavioral_segments
	SELECT @col

	RETURN
END

SELECT * FROM calc_behavioral_segment();;
Copy
BEHAVIORAL_SEGMENT|
------------------+
           Unknown|

Copy

Inserir valor de acordo com a instrução if/else

Insere uma linha na tabela de acordo com a condição e retorna a tabela com o novo valor

Transact-SQL
 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);
Copy
       NUMBER_TYPE|
------------------+
               Odd|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION odd_or_even_number (NUMBER INT)
RETURNS NUMBERS TABLE (
	number_type VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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);;
Copy
       NUMBER_TYPE|
------------------+
               Odd|

Copy

Insere múltiplos de acordo com a instrução if/else

O exemplo abaixo insere mais de um valor na tabela e mais de uma variável é modificada de acordo com a condição. Retorna a tabela com os novos valores

Transact-SQL
 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');
Copy
   ID_EMPLOYEE|  WORKING_FROM_HOME|     TEAM|   COMPUTER|
--------------+-------------------|---------|-----------|
     123456789|                  1|   TEAM_1|     LAPTOP|

Copy
Snowflake
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION new_employee_hired (ID STRING, POSITION STRING, EXPERIENCE STRING)
RETURNS NEW_EMPLOYEE TABLE (
	id_employee VARCHAR(50),
	working_from_home BOOLEAN,
	team VARCHAR(15),
	computer VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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');;
Copy
   ID_EMPLOYEE|  WORKING_FROM_HOME|     TEAM|   COMPUTER|
--------------+-------------------|---------|-----------|
     123456789|                  1|   TEAM_1|     LAPTOP|

Copy

Aviso

Se houver instruções if aninhadas e mais de uma variável for modificada nas instruções, será necessário usar um procedimento armazenado.

Atualizar valores inseridos anteriormente

Atualiza os valores das colunas da tabela no corpo da função e o retorna com os novos valores.

Transact-SQL
 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();
Copy
                      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|


Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_employees_history ()
RETURNS EMPLOYEE_HISTORY 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
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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();;
Copy
                      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|


Copy

Múltiplas cláusulas de retorno

No exemplo a seguir, há mais de uma cláusula de retorno, porque, dependendo da situação, não é necessário continuar executando a função inteira.

Transact-SQL
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 &#x3C; 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');             
Copy
          TYPE|           NAME|
--------------+ --------------+
    SMALL_TEAM|          TEAM1|
 

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION create_new_team (TEAM_NAME STRING)
RETURNS NEW_TEAM TABLE (
	type VARCHAR(50),
	name VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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');;
Copy
          TYPE|           NAME|
--------------+ --------------+
    SMALL_TEAM|          TEAM1|

Copy

Aviso

Essa transformação é aplicada quando há apenas um valor a ser inserido; se houver mais de um valor, será necessário usar um procedimento armazenado.

Casos complexos

O exemplo é um caso complexo que usa instruções aninhadas if e insere um valor dependendo da condição verdadeira.

Transact-SQL
 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')
Copy
  VACATION_STATUS|
-----------------+
               OK|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION vacation_status (ID STRING)
RETURNS STATUS TABLE (
	vacation_status VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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');
Copy
  VACATION_STATUS|
-----------------+
               OK|

Copy

Problemas conhecidos

Instruções while ao lado de consultas

O problema com esse exemplo é que não há como transformar a instrução while em CTE dentro da cláusula WITH do Select principal, o que nos força a transformar essa instrução em procedimento armazenado para manter a mesma lógica.

Transact-SQL
--Additional Params: -t JavaScript
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 &#x3C; 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);
Copy
        GROUP_NAME|
------------------+
       Tool Design|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_group_name
(DEPARTMENT_ID INT)
RETURNS @group_names TABLE (
	group_name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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);;
Copy
        GROUP_NAME|
------------------+
       Tool Design|

Copy

Declare Cursor

As funções definidas pelo usuário não podem DECLARE, OPEN, FETCH, CLOSE ou DEALLOCATE um CURSOR. Use um procedimento armazenado para trabalhar com cursores.

Transact-SQL
 --Additional Params: -t JavaScript

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);
Copy
            AMOUNT|
------------------+
                 3|

Copy
Snowflake SQL
 --Additional Params: -t JavaScript
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!

CREATE OR ALTER FUNCTION amount_new_specimens (ID INT)
RETURNS @new_specimens TABLE (
	amount INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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);;
Copy
            AMOUNT|
------------------+
                 3|

Copy

Não há suporte para instruções diferentes em Expressões de tabela comuns

As cláusulas UPDATE, INSERT, DELETE, ALTER ou DROP não são compatíveis com o corpo de expressões de tabela comuns, mesmo após sua declaração usando um delimitador. Por esse motivo, a função pode ser modificada para funcionar como um procedimento armazenado.

Transact-SQL
 --Additional Params: -t JavaScript

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
Copy
                      PRODUCT_NAME|            Rating|
----------------------------------+------------------|
                 HL Mountain Pedal|                 3|
            Mountain Bike Socks, M|                 5|
             Road-550-W Yellow, 40|                 5|

Copy
Snowflake SQL
 CREATE OR REPLACE PROCEDURE product_history ()
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
	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`);
$$;

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

CALL product_history();
Copy
                      PRODUCT_NAME|            Rating|
----------------------------------+------------------|
                 HL Mountain Pedal|                 3|
            Mountain Bike Socks, M|                 5|
             Road-550-W Yellow, 40|                 5|

Copy

EWIs relacionados

  1. SSC-EWI-0040: Instrução não suportada.

  2. SSC-EWI-0073: Revisão de equivalência funcional pendente

SCALAR

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Descrição

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Uma função escalar definida pelo usuário é uma rotina Transact-SQL ou de tempo de execução de linguagem comum (CLR) que aceita parâmetros, executa uma ação, como um cálculo complexo, e retorna o resultado dessa ação como um valor escalar. (Referência da linguagem SQL Server - Subseção CREATE FUNCTION).

Nota

Essas funções geralmente são usadas dentro da instrução SELECTou na configuração de uma única variável (provavelmente dentro de um procedimento armazenado).

Sintaxe do Transact-SQL

 -- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
 [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]
Copy

Sintaxe do Snowflake

O Snowflake permite 3 linguagens diferentes em suas funções definidas pelo usuário:

  • SQL

  • JavaScript

  • Java

Por enquanto, o SnowConvert será compatível apenas com SQL e JavaScript como linguagens de destino.

Nota

Funções definidas pelo usuário do SQL suportam apenas uma consulta como seu corpo. Elas podem ler o banco de dados, mas não têm permissão para gravá-lo ou modificá-lo. (Referência Scalar SQL UDFs).

 CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'
Copy

Nota

Funções definidas pelo usuário do JavaScript permitem várias instruções em seus corpos, mas não podem realizar consultas ao banco de dados. (Referência Scalar JavaScriptUDFs)

 CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'
Copy

Amostra de padrões da origem

Instruções Set e Declare

As instruções mais comuns nos corpos das funções são as instruções DECLARE e SET. Para instruções DECLARE sem valor padrão, a transformação será ignorada. As instruções SET e DECLARE com um valor padrão serão transformadas em COMMON TABLE EXPRESSION. Cada expressão de tabela comum conterá uma coluna que representa o valor da variável local.

Transact-SQL
 CREATE OR ALTER FUNCTION PURCHASING.GetVendorName()
RETURNS NVARCHAR(50) AS
BEGIN
	DECLARE @result NVARCHAR(50)
	DECLARE @BUSINESSENTITYID INT
	
	SET @BUSINESSENTITYID = 1492
	
	SELECT @result = Name FROM PURCHASING.VENDOR WHERE BUSINESSENTITYID = @BUSINESSENTITYID
	
	RETURN @result
END

GO

SELECT PURCHASING.GetVendorName() as vendor_name;
Copy
vendor_name            | 
-----------------------+ 
Australia Bike Retailer|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.GetVendorName ()
RETURNS VARCHAR(50)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			1492 AS BUSINESSENTITYID
	),
	CTE2 AS
	(
		SELECT
			Name AS RESULT
		FROM
			PURCHASING.VENDOR
		WHERE
			BUSINESSENTITYID = (
				SELECT
					BUSINESSENTITYID
				FROM
					CTE1
			)
	)
	SELECT
		RESULT
	FROM
		CTE2
$$;

SELECT
	PURCHASING.GetVendorName() as vendor_name;
Copy
VENDOR_NAME            |
-----------------------+
Australia Bike Retailer|

Copy

Transformação da instrução If/Else

As instruções If/Else podem ser tratadas de diferentes maneiras: elas podem ser transformadas em Javascript ou em SQL usando CASE EXPRESSION dentro de Select permitindo condicionais dentro de consultas, enquanto a transformação em Javascript é bastante simples, a instrução Case pode não ser tão óbvia à primeira vista.

Transact-SQL
 CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
	DECLARE @result VARCHAR(10)
	DECLARE @ActiveFlag BIT
	
	SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
	
	IF @ActiveFlag = 1
		SET @result = 'YES'
	ELSE IF @ActiveFlag = 0 
		SET @result = 'NO'
	
	RETURN @result
END

GO

SELECT PURCHASING.HasActiveFlag(1516) as has_active_flag;
Copy
has_active_flag|
---------------+
NO             |

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (P_BUSINESSENTITYID INT)
RETURNS VARCHAR(10)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(

		SELECT
			ActiveFlag AS ACTIVEFLAG
		from
			PURCHASING.VENDOR v
		where
			v.BUSINESSENTITYID = P_BUSINESSENTITYID
	),
	CTE2 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						ACTIVEFLAG
					FROM
						CTE1
				) = 1
					THEN 'YES'
				WHEN (
					SELECT
						ACTIVEFLAG
					FROM
						CTE1
				) = 0
					THEN 'NO'
			END AS RESULT
	)
	SELECT
		RESULT
	FROM
		CTE2
$$;

SELECT
	PURCHASING.HasActiveFlag(1516) as has_active_flag;
Copy
HAS_ACTIVE_FLAG|
---------------+
NO             |

Copy

Instruções aninhadas

Para instruções aninhadas, a programação estruturada está sendo transformada em uma única consulta. As instruções no controle de fluxo serão aninhadas em estruturas de tabela para preservar a ordem de execução.

Nota

CASE EXPRESSIONS só pode retornar um valor por instrução

Exemplo

Nota

O código a seguir em ambos os paradigmas de programação é funcionalmente equivalente.

 DECLARE @VendorId AS int;
DECLARE @AccountNumber AS VARCHAR(50);
SELECT @VendorId = poh.VendorID 
    FROM Purchasing.PurchaseOrderHeader poh
    WHERE PurchaseOrderID = 1
SELECT @AccountNumber = v.AccountNumber
    FROM Purchasing.Vendor v
    WHERE v.BusinessEntityID = @VendorId
Copy
 SELECT V.AccountNumber AccountNumber
FROM (SELECT poh.VendorID VendorId 
         FROM Purchasing.PurchaseOrderHeader poh
         WHERE PurchaseOrderID = 1
) T1, Purchasing.Vendor v
WHERE v.BusinessEntityID = T1.VendorId
Copy
AccountNumber|
-------------+
LITWARE0001  |

Copy

Variáveis condicionais por meio de SELECTs

A definição e a atribuição de variáveis em instruções condicionais tendem a ser um pouco problemáticas, pois as referências à variável mais adiante no código precisariam saber onde a variável foi modificada pela última vez. Além disso, se a referência estiver dentro de outra instrução condicional, terá de haver algum tipo de redirecionamento que faça referência à atribuição conhecida anterior à variável.

Isso tudo é agravado pelo aninhamento e pelas consultas complexas que podem ser encontradas no código de entrada.

No cenário a seguir, a primeira instrução IF pode ser transformada sem problemas, pois o conteúdo é bastante simples. A segunda e a terceira instruções IF estão comentadas porque não são compatíveis no momento, já que há instruções que não são atribuições de variáveis por meio de SELECT.

SQL Server
 CREATE or ALTER FUNCTION PURCHASING.SELECTINUDF (
    @param1 varchar(12)
)
RETURNS int
AS
BEGIN
    declare @var1 int;
    declare @var2 int;
    declare @var3 int;

    IF @param1 = 'first'
    BEGIN
        select @var1 = col1 + 10 from table1 WHERE id = 0;
        select @var2 = col1 + 20 from table1 WHERE id = 0;
        select @var3 = col1 + 30 from table1 WHERE id = 0;
    END

    IF @param1 = 'second'
    BEGIN
        declare @var4 int = 10;
        select @var1 = col1 + 40 from table1 WHERE id = 0;
        select @var2 = col1 + 40 from table1 WHERE id = 0;
    END

    IF @param1 = 'third'
    BEGIN
        select col1 from table1 where id = 0;
        select @var1 = col1 + 50 from table1 WHERE id = 0;
        select @var2 = col1 + 50 from table1 WHERE id = 0;
    END

    RETURN @var1
END

SELECT PURCHASING.SELECTINUDF('first') as result; -- Assuming table1.col1 is 0 when ID = 0
Copy
RESULT|
------+
    10|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.SELECTINUDF (PARAM1 STRING)
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
    WITH CTE1 AS
    (
        SELECT
            CASE
                WHEN PARAM1 = 'first'
                    THEN (SELECT
                        col1 + 10 AS VAR1 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR1,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 20 AS VAR2 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR2,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 30 AS VAR3 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR3
    ),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
    CTE2 AS
    (
        /*    IF @param1 = 'second'
            BEGIN
                declare @var4 int = 10;
                select @var1 = col1 + 40 from table1 WHERE id = 0;
                select @var2 = col1 + 40 from table1 WHERE id = 0;
            END*/
        SELECT
            null
    ),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
    CTE3 AS
    (
        /*    IF @param1 = 'third'
            BEGIN
                select col1 from table1 where id = 0;
                select @var1 = col1 + 50 from table1 WHERE id = 0;
                select @var2 = col1 + 50 from table1 WHERE id = 0;
            END*/
        SELECT
            null
    ),
    CTE4 AS
    (

        SELECT
            PURCHASING.SELECTINUDF('first') as result
    )
    SELECT
        VAR1
    FROM
        CTE4
$$ -- Assuming table1.col1 is 0 when ID = 0
;
Copy
RESULT|
------+
    10|

Copy

Atribuir e retornar uma variável

Nesse padrão simples, há uma declaração de variável e, em seguida, essa variável é definida usando uma instrução SELECT e, por fim, retornada. Isso será migrado para uma Expressão de tabela comum para manter o comportamento original.

SQL Server
 CREATE OR ALTER FUNCTION Purchasing.GetTotalFreight()
RETURNS MONEY AS
BEGIN
	DECLARE @Result MONEY
	SELECT @Result = ISNULL(SUM(t.Freight), 0) from Purchasing.PurchaseOrderHeader t
	return @Result
END

GO

select Purchasing.GetTotalFreight() as Result;
Copy
Result      |
------------+
1583978.2263|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION Purchasing.GetTotalFreight ()
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
			NVL(SUM(t.Freight), 0) AS RESULT from
			Purchasing.PurchaseOrderHeader t
	)
	SELECT
		RESULT
	FROM
		CTE1
$$;

select
	Purchasing.GetTotalFreight() as Result;
Copy
RESULT      |
------------+
1583978.2263|

Copy

Chamadas de funções múltiplas

Para esse padrão específico, não há consultas óbvias, mas há várias chamadas para várias funções que trabalham com a mesma variável e a retornam no final. Como o Snowflake só oferece suporte a consultas dentro de suas funções, a solução para esse bloco será adicioná-lo a um Select e aninhar as chamadas dentro dele, certificando-se de que o valor de retorno seja o mesmo que o da origem.

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.Foo
(
	@PARAM1	INT
)
RETURNS varchar(25)
AS
BEGIN
	DECLARE @filter INT = @PARAM1
	DECLARE @NAME VARCHAR(25) = (SELECT Name from Purchasing.Vendor v where BusinessEntityID = @filter) 
	SET @NAME = REPLACE(@NAME, 'Australia', 'USA')
	SET @NAME = REPLACE(@NAME, 'Bike', 'Car')
	RETURN @NAME
END

GO

SELECT PURCHASING.Foo(1492) AS Name;
Copy
Name            |
----------------+
USA Car Retailer|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.Foo (PARAM1 INT)
RETURNS VARCHAR(25)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			PARAM1 AS FILTER
	),
	CTE2 AS
	(
		SELECT
			(SELECT
					Name
				from
					Purchasing.Vendor v
				where
					BusinessEntityID = (
						SELECT
							FILTER
						FROM
							CTE1
					)
			) AS NAME
	),
	CTE3 AS
	(
		SELECT
			REPLACE((
				SELECT
					NAME
				FROM
					CTE3
			), 'Australia', 'USA') AS NAME
	),
	CTE4 AS
	(
		SELECT
			REPLACE((
				SELECT
					NAME
				FROM
					CTE4
			), 'Bike', 'Car') AS NAME
	)
	SELECT
		NAME
	FROM
		CTE4
$$;

SELECT
	PURCHASING.Foo(1492) AS Name;
Copy
NAME            |
----------------+
USA Car Retailer|

Copy

Aumentar uma variável com base em várias condições IF e retornar seu valor

Para esse padrão, uma variável é modificada (aumentada, neste caso) usando várias condições IF. No início, um conjunto de variáveis é inicializado e usado para determinar se a variável de resultado deve ser aumentada ou não. Por fim, a variável de resultado é retornada.

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.FOO() 
RETURNS MONEY
AS
BEGIN
	declare @firstValue MONEY
	declare @secondValue MONEY
	declare @Result MONEY
	select  @Result = 0
	select 	@firstValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 1
	select 	@secondValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 2
	if @firstValue is not null
		select @Result = @Result + @firstValue
	if @secondValue is not null
		select @Result = @Result + @secondValue
	return @Result 
END

GO

SELECT PURCHASING.Foo() AS Result;
Copy
Result  |
--------+
473.1415|

Copy
Snowflake
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
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
			0 AS RESULT
	),
	CTE2 AS
	(
		select
			SubTotal AS FIRSTVALUE
		from
			Purchasing.PurchaseOrderHeader
		where
			PurchaseOrderID = 1
	),
	CTE3 AS
	(
		select
			SubTotal AS SECONDVALUE
		from
			Purchasing.PurchaseOrderHeader
		where
			PurchaseOrderID = 2
	),
	CTE4 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						FIRSTVALUE
					FROM
						CTE2
				) is not null
					THEN (
					select
						(
							SELECT
								RESULT
							FROM
								CTE1
						) + (
							SELECT
								FIRSTVALUE
							FROM
								CTE2
						) AS RESULT)
			END AS RESULT
	),
	CTE5 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						SECONDVALUE
					FROM
						CTE3
				) is not null
					THEN (
					select
						(
							SELECT
								RESULT
							FROM
								CTE1
						) + (
							SELECT
								SECONDVALUE
							FROM
								CTE3
						) AS RESULT)
				ELSE (SELECT
					RESULT
				FROM
					CTE4)
			END AS RESULT
	)
	SELECT
		RESULT
	FROM
		CTE5
$$;

SELECT
	PURCHASING.Foo() AS Result;
Copy
RESULT  |
--------+
473.1415|

Copy

Duas ou mais instruções RETURN

Para esse padrão, o bloco IF que contém a cláusula de retorno que interrompe o fluxo do código é adicionado ao final do corpo, como a instrução final a ser executada em uma expressão CASE.

Case básico

Para esse cenário específico, não há lógica entre a instrução condicional RETURN e a instrução final RETURN, portanto, todo o corpo será mapeado para um único CASE EXPRESSION.

SQL Server
 CREATE OR ALTER FUNCTION [PURCHASING].[FOO] ()
RETURNS INT
AS
BEGIN
	IF exists (SELECT PreferredVendorStatus FROM Purchasing.Vendor v )
		RETURN 1

	RETURN 0
END

GO

SELECT PURCHASING.FOO() as result;
Copy
result|
------+
     1|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	SELECT
		CASE
			WHEN exists (SELECT
					PreferredVendorStatus
				FROM
					Purchasing.Vendor v
			)
				THEN 1
			ELSE 0
		END
$$;

SELECT
	PURCHASING.FOO() as result;
Copy
RESULT|
------+
     1|

Copy

Expressões de tabela comuns

As expressões de tabela comuns serão mantidas como no código original e serão concatenadas com as expressões geradas. O SnowConvert é capaz de identificar primeiro todos os nomes originais de COMMON TABLE EXPRESSION para evitar a geração de nomes duplicados.

SQL Server
 CREATE OR ALTER FUNCTION [PURCHASING].[FOO] 
(
	@status INT
) 
Returns INT
As
Begin 
	Declare @result as int = 0

	;WITH ctetable(RevisionNumber) as 
	(
		SELECT RevisionNumber
		FROM Purchasing.PurchaseOrderHeader poh
		where poh.Status = @status
	),
	finalCte As
	(
		SELECT RevisionNumber FROM ctetable	
	)
	
	Select @result = count(RevisionNumber) from finalCte	
	return @result;
End

GO

SELECT PURCHASING.FOO(4) as result;
Copy
result|
------+
  3689|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.FOO (STATUS INT)
Returns INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			0 AS RESULT
	),
	ctetable (
		RevisionNumber
	) as
		(
			SELECT
			RevisionNumber
			FROM
			Purchasing.PurchaseOrderHeader poh
			where
			poh.Status = STATUS
		),
		finalCte As
		(
			SELECT
			RevisionNumber
		FROM
			ctetable
		),
		CTE2 AS
		(
		Select
			COUNT(RevisionNumber) AS RESULT from
			finalCte
		)
		SELECT
		RESULT
		FROM
		CTE2
$$;

SELECT
		PURCHASING.FOO(4) as result;
Copy
RESULT|
------+
  3689|

Copy

Transformar em JavaScript UDFs

Se houver várias instruções e a função não acessar o banco de dados de forma alguma, ela poderá ser transformada em uma função JavaScript mantendo a equivalência funcional

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.GetFiscalYear 
(
	@DATE AS DATETIME
)
RETURNS INT
AS
BEGIN
	DECLARE @FiscalYear AS INT
	DECLARE @CurMonth AS INT
	SET @CurMonth = DATEPART(M,@DATE)
	SET @FiscalYear = DATEPART(YYYY, @DATE)
	IF (@CurMonth >= 7)
	BEGIN
		SET @FiscalYear = @FiscalYear + 1 
	END
	RETURN @FiscalYear
END

GO

SELECT PURCHASING.GetFiscalYear('2020-10-10') as DATE;
Copy
 CREATE OR ALTER FUNCTION PURCHASING.[getCleanChargeCode]
(
	@ChargeCode varchar(50)
)
returns varchar(50) as
begin
	declare @CleanChargeCode varchar(50),@Len int,@Pos int=2
	set @Pos=LEN(@ChargeCode)-1
	while @Pos > 1
	begin
		set @CleanChargeCode=RIGHT(@ChargeCode,@Pos)
		if TRY_CAST(@CleanChargeCode as bigint) is not null
			return @CleanChargeCode
		set @Pos=@Pos-1
	end
	set @Pos=LEN(@ChargeCode)-1
	while @Pos > 1
	begin
		set @CleanChargeCode=LEFT(@ChargeCode,@Pos)
		if TRY_CAST(@CleanChargeCode as bigint) is not null
			return @CleanChargeCode
		set @Pos=@Pos-1
	end
	return null
end

GO

SELECT PURCHASING.[getCleanChargeCode]('16test') AS CleanChargeCode;
Copy
DATE|
----+
2021|

Copy
CleanChargeCode|
---------------+
16             |

Copy
Snowflake
 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.GetFiscalYear (DATE TIMESTAMP_NTZ(3))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		FISCALYEAR INT;
		CURMONTH INT;
	BEGIN
		 
		 
		CURMONTH := DATE_PART(month, :DATE :: TIMESTAMP);
		FISCALYEAR := DATE_PART(year, :DATE :: TIMESTAMP);
		IF ((:CURMONTH >= 7)) THEN
			BEGIN
				FISCALYEAR := :FISCALYEAR + 1;
			END;
		END IF;
		RETURN :FISCALYEAR;
	END;
$$;


SELECT
	!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GetFiscalYear' NODE ***/!!!
	PURCHASING.GetFiscalYear('2020-10-10') as DATE;
Copy
 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.getCleanChargeCode (CHARGECODE STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		CLEANCHARGECODE VARCHAR(50);
		LEN INT;
		POS INT := 2;
	BEGIN
		 
		POS := LEN(:CHARGECODE)-1;
		WHILE (:POS > 1) LOOP
			CLEANCHARGECODE := RIGHT(:CHARGECODE, :POS);
			IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
				RETURN :CLEANCHARGECODE;
			END IF;
			POS := :POS -1;
		END LOOP;
		POS := LEN(:CHARGECODE)-1;
		WHILE (:POS > 1) LOOP
			CLEANCHARGECODE := LEFT(:CHARGECODE, :POS);
			IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
				RETURN :CLEANCHARGECODE;
			END IF;
			POS := :POS -1;
		END LOOP;
		RETURN null;
	END;
$$;

SELECT
	PURCHASING.getCleanChargeCode('16test') !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! AS CleanChargeCode;
Copy
DATE  |
------+
2021.0|

Copy
CLEANCHARGECODE|
---------------+
16             |

Copy

Problemas conhecidos

Aviso

As funções definidas pelo usuário não podem ser usadas para executar ações que modifiquem o estado do banco de dados

Aviso

As funções definidas pelo usuário não podem conter uma cláusula OUTPUT INTO que tenha uma tabela como destino

Aviso

As funções definidas pelo usuário não podem DECLARE, OPEN, FETCH, CLOSE ou DEALLOCATE um CURSOR. Use um procedimento armazenado se você precisar usar cursores.

Aviso

As funções definidas pelo usuário não podem executar instruções de controle de fluxo, como WHILE, se houver pelo menos uma chamada ao banco de dados

Aviso

As funções definidas pelo usuário com referências a outras funções definidas pelo usuário que foram transformadas em procedimentos armazenados também serão transformadas em procedimentos armazenados.

Aviso

As funções definidas pelo usuário que usam @@ROWCOUNT não são suportadas no SQL e devem ser transformadas em procedimentos armazenados para manter a equivalência funcional.

Aviso

As funções definidas pelo usuário que têm instruções SELECT atribuindo uma variável a si mesmas não são compatíveis com o Snowflake. Consulte também SELECT @local_variable

Para todos os casos sem suporte, consulte o EWIs relacionado e os padrões abaixo para obter recomendações e possíveis soluções alternativas.

Condicionais que não sejam instruções if/else ao lado de consultas

O próximo cenário envolve o uso da instrução «while» junto com outras consultas. O problema com esse exemplo é que não há como transformar a instrução while em CTE dentro da cláusula WITH de Select principal, o que nos força a transformar essa instrução em um procedimento JavaScript para manter a mesma lógica.

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
    DECLARE @i int = 0, @p int;
    Select @p = COUNT(*) FROM PURCHASING.VENDOR
    
    WHILE (@p < 1000)
    BEGIN
        SET @i = @i + 1
        SET @p = @p + @i
    END
        
    IF (@i = 6)
        RETURN 1
    
    RETURN @p
END

GO

SELECT PURCHASING.FOO() as result;
Copy
result|
------+
  1007|

Copy

Snowflake

 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I INT := 0;
        P INT;
    BEGIN
         
        Select
            COUNT(*)
        INTO
            :P
 FROM
            PURCHASING.VENDOR;
        WHILE (:P < 1000) LOOP
            I := :I + 1;
            P := :P + :I;
        END LOOP;
        IF ((:I = 6)) THEN
            RETURN 1;
        END IF;
        RETURN :P;
    END;
$$;

SELECT
    PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as result;
Copy
FOO |
----+
1007|

Copy

Atribuir uma variável usando seu próprio valor ao iterar por um conjunto de linhas

No exemplo a seguir, a variável @names é usada para concatenar vários valores de uma coluna em uma única cadeia de caracteres. A variável é atualizada em cada iteração, conforme mostrado, o que não é suportado pelos SnowFlake UDFs. Para esse cenário, a função deve ser transformada em um procedimento.

SQL Server

 CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @names varchar(8000)
    SET @names = ''
    SELECT @names = ISNULL(@names + ' ', '') + Name from Purchasing.Vendor v 
    return @names              
END

GO

select PURCHASING.FOO() as names;
Copy
names                                                                                                                                                                                                                                                          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Australia Bike Retailer Allenson Cycles Advanced Bicycles Trikes, Inc. Morgan Bike Accessories Cycling Master Chicago Rent-All Greenwood Athletic Company Compete Enterprises, Inc International Light Speed Training Systems Gardner Touring Cycles Internati|

Copy

Consulta do Snowflake

 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        NAMES VARCHAR(8000);
    BEGIN
         
        NAMES := '';
        SELECT
            NVL(:NAMES || ' ', 0) + Name
        INTO
            :NAMES
        from
            Purchasing.Vendor v;
        RETURN :NAMES;
    END;
$$;

select
    PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as names;
Copy

Aviso

Para os cenários descritos acima, considere as seguintes limitações:

  1. Todas as chamadas para funções definidas pelo usuário nas consultas DML, como SELECT, INSERT, DELETE, UPDATE ou MERGE, falharão porque não são permitidas chamadas para procedimentos armazenados nessas consultas.

  2. As chamadas para funções definidas pelo usuário dentro de procedimentos devem ser precedidas pela palavra-chave CALL.

  3. As funções definidas pelo usuário usadas em COMPUTED COLUMNS falharão durante a execução.

EWIs relacionados

  1. SSC-EWI-0067: UDF foi transformado em um procedimento Snowflake, não há suporte para a chamada de procedimentos dentro de uma consulta.

  2. SSC-EWI-0073: Revisão de equivalência funcional pendente.

  3. SSC-FDM-0029: A função definida pelo usuário foi transformada em um procedimento do Snowflake.