SnowConvert : DDLs Transact

Référence de traduction pour toutes les instructions DDL qui sont précédées du mot CREATE.

Index

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Avertissement

Actuellement, l’instruction _ Create Index _ n’est pas convertie mais elle est analysée. De même, si votre code source comporte des instructions create index, celles-ci seront prises en compte dans le _ rapport d’évaluation. _

Exemple de Create Index

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

Note

Pour des raisons architecturales, Snowflake ne prend pas en charge les index. Par conséquent, SnowConvert supprimera tout le code lié à la création d’index. Snowflake crée automatiquement des micro-partitions pour chaque table afin d’accélérer les performances des opérations DML. L’utilisateur n’a pas à se préoccuper de la création ou de la gestion de ces micro-partitions.

En général, cela suffit pour obtenir de très bonnes performances de requête, mais il est possible de les améliorer en créant des clés de clustering de données. La page officielle de Snowflake fournit plus d’informations sur les micro-partitions et le clustering de données.

Vue matérialisée

Applies to
  • [x] Azure Synapse Analytics

Description

Dans Snowconvert, les vues matérialisées sont transformées en tables dynamiques Snowflake. Pour configurer correctement les tables dynamiques, deux paramètres essentiels doivent être définis : TARGET\LAG et WAREHOUSE. Si ces paramètres ne sont pas spécifiés dans les options de configuration, Snowconvert utilisera par défaut des valeurs prédéfinies lors de la conversion, comme le montre l’exemple ci-dessous.

Pour plus d’informations sur les vues matérialisées, cliquez ici.

Pour plus de détails sur les paramètres nécessaires pour les tables dynamiques, cliquez ici.

Modèles d’échantillons de sources

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

Problèmes connus

Aucune erreur connue n’a été détectée pour le moment.

Procédures

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

1. CREATE PROCEDURE Translation

Snowflake CREATE PROCEDURE est défini en syntaxe SQL tandis que ses instructions internes sont définies en JavaScript.

Code source :

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

Code traduit :

 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

DATA TYPE du paramètre

Les types de données des paramètres sont traduits en équivalents Snowflake. Voir aussi Types de données.

Assistant EXEC

Pour pouvoir exécuter les instructions d’une procédure dans l’environnement SnowFlake, ces instructions doivent être prétraitées et adaptées pour refléter leur exécution dans plusieurs variables spécifiques à la langue source.

SnowConvert traduit automatiquement les instructions prises en charge et utilise un assistant EXEC. Ce fournisseur offre des possibilités d’accès et de mise à jour à de nombreuses variables qui simulent l’exécution de ces instructions dans leur environnement natif.

Par exemple, vous pouvez constater que, dans les procédures migrées, un bloc de code est toujours ajouté. Nous allons expliquer la structure de base de ce code dans la section suivante. Gardez à l’esprit que nous évaluons en permanence de nouvelles façons d’optimiser les transformations et les outils d’assistance nécessaires.

Structure

La structure de base de l’aide EXEC est la suivante :

  1. Section de déclaration des variables : On déclare ici les différentes variables ou objets qui contiendront les valeurs associées à l’exécution des instructions à l’intérieur de la procédure. Il s’agit de valeurs telles que le nombre de lignes affectées par une instruction, voire le jeu de résultats lui-même.

  2. Déclaration de la fonction fixBind : Il s’agit d’une fonction auxiliaire utilisée pour corriger les liaisons lorsqu’elles sont de type Date.

  3. Déclaration de la fonction EXEC : Il s’agit de la fonction d’assistance principale de EXEC. Elle reçoit l’instruction à exécuter, le tableau des liaisons (essentiellement les variables ou paramètres qui peuvent être modifiés par l’exécution et qui nécessitent la permanence des données tout au long de l’exécution de la procédure), le drapeau noCatch qui détermine si ERROR_HANDLERS doivent être utilisés, et la fonction catchFunction pour l’exécution du code personnalisé en cas d’exception lors de l’exécution de l’instruction. Le corps de la fonction EXEC est très simple : il s’agit d’exécuter l’instruction et de stocker toutes les données utiles produites par son exécution, le tout à l’intérieur d’un bloc de gestion des erreurs.

  4. ERROR VARS : Le bloc de capture EXEC établit une liste de variables d’erreur telles que MESSAGE_TEXT, SQLCODE, SQLSTATE, PROC_NAME et ERROR_LINE qui pourraient être utilisées pour récupérer les valeurs des fonctions définies par l’utilisateur, afin d’émuler le comportement des fonctions intégrées SQL Server ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE et ERROR_STATE . Une fois que l’ensemble de ces variables a été définie sur une valeur, la fonction définie par l’utilisateur UPDATE_ERROR_VARS sera chargée de mettre à jour certaines variables d’environnement avec les valeurs d’erreur, afin d’y avoir accès dans le champ d’application SQL.

Code

Le bloc de code suivant représente l’assitant EXEC à l’intérieur d’une procédure :

    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

Exemple EXEC simple

Voici un exemple simple d’un appel à EXEC à l’intérieur d’une procédure stockée

Code source

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

Code attendu

 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 dans une procédure stockée avec un paramètre

Dans cet exemple, la commande EXEC se trouve à l’intérieur d’une procédure stockée et reçoit une valeur de paramètre

Code source

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

Code attendu

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 invoquant une procédure stockée avec un paramètre

Dans cet exemple, EXEC invoque une autre procédure stockée et ajoute un paramètre

Code source

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

Code attendu

 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

Paramètres avec valeur par défaut.

Dans SqlServer, certains paramètres peuvent avoir une valeur par défaut au cas où ils ne seraient pas spécifiés lors de l’appel d’une procédure.

Par exemple

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

Traduit dans Snowflake par

 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

Assistant 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

Assistant EXEC Insert Into

L’assistant Exec Insert into génère une fonction appelée Insert insertIntoTemporaryTable(sql). Cette fonction permettra à la transformation pour INSERT INTO TABLE_NAME EXEC(...) de TSQL à Snowflake d’imiter le comportement de l’instruction originale en insérant les données dans une table temporaire, puis en les réinsérant dans l’instruction originale.

Pour plus d’informations sur la manière dont le code de cette instruction est modifié, consultez la section relative à l’insertion dans l’exécution

Note

Ce code généré pour INSERT INTO EXEC, peut présenter des problèmes de performance lors du traitement des instructions EXECUTE contenant plusieurs requêtes.

   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

Assistant LIKE

Dans le cas où une expression similaire est trouvée dans une procédure, par exemple

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

Comme l’intérieur de la procédure est transformé en javascript, l’expression like provoquera une erreur. Afin d’éviter et de conserver la fonctionnalité, une fonction est ajoutée au début de la procédure si une expression similaire est trouvée.

   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

Cette fonction permet de répliquer la fonctionnalité de l’expression like de sql. Voyons les différents cas où elle peut être utilisée

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

Dans le dernier code, il y a un normal like, un not like et un like avec échappement. La transformation sera

 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

Notez que les fonctionnalités sont transformées en appels de fonctions

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

Les paramètres que la fonction LIKE reçoit sont les suivants :

  • L’expression en cours d’évaluation.

  • Le modèle de comparaison

  • S’il est présent, le caractère d’échappement, ce paramètre est facultatif.

Assistant Select

Génère une fonction appelée SELECT lorsqu’une valeur scalaire doit être définie dans une variable

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

Dans ce cas, il générera le code suivant avec l’assistant 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

L’assistant SELECT pourrait également être utilisé pour insérer dans une valeur locale une valeur récupérée à partir d’une requête. L’assistant a été conçu spécifiquement pour prendre en charge le même comportement que SQL Server SELECT @local_variable. Le paramètre args représente chaque opération appliquée à toutes les variables locales à l’intérieur de select. Voir aussi SELECT @Variable. Par exemple :

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

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

GO
Copy

Dans ce cas, les assignations de variables seront traduites en JavaScript lambdas afin d’émuler le comportement de 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

Assistant RAISERROR

Cette aide est générée lorsqu’il existe des utilisations d’un appel à RAISERROR dans le code source. Exemple :

 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

RAISERROR exécute la commande UPDATE_ERROR_VARS_UDF afin de stocker la valeur du message d’erreur, la gravité et l’état en tant que variables d’environnement, au cas où elles devraient être utilisées en appelant l’une des fonctions intégrées ERROR. Enfin, le message d’erreur est envoyé dans le même format que celui de SQL Server.

Assistant de la fonction Identify

Cette aide est générée chaque fois que la fonction Identify est utilisée sur un Select Into à l’intérieur d’une procédure.

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

Les paramètres de cet assistant sont les mêmes que ceux de la fonction originale, elle est créée afin de générer une séquence imitant le comportement de la fonction Identify dans TSQL, les changements apportés au code original sont les suivants :

  • Un appel de méthode supplémentaire à la fonction IdentityHelper en utilisant les mêmes paramètres que ceux trouvés dans le code source.

  • Et appel à la conception d’une fonction IDENTITY_UDF pour obtenir la valeur suivante dans la séquence.

    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

Comme dans TSQL, si aucun paramètre n’est donné, les valeurs par défaut seront (1,1).

Aide à la procédure CALL

Cet assistant est généré chaque fois qu’il y a un appel à ce qui était auparavant une fonction définie par l’utilisateur, mais qui est maintenant une procédure à la suite du processus de traduction.

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

L’objectif de cet assistant est d’encapsuler la logique requise pour appeler des procédures comme s’il s’agissait de fonctions.

N’oubliez pas que cette fonctionnalité est limitée, car les procédures ne peuvent pas être invoquées dans le cadre de requêtes telles que SELECT.

Exemple d’utilisation, en supposant que FooSelfAssign(@PAR INT) ait été traduit en procédure :

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

Notez que la traduction de VAR1 est très simple, mais que pour VAR4, le CALL le plus éloigné contient une liste avec le reste des CALLs, en tant qu’annonces.

Chaque CALL successif est traduit en une liaison, s’il est contenu dans un autre CALL.

2. Variables

DECLARE @Variable

Code source

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

Code traduit

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

DECLARE @Variable Table

Dans ce cas, DECLARE est utilisé pour déclarer une table de variables, voyons un exemple.

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

Si nous exécutons ce code dans le serveur Sql, nous obtiendrons le résultat suivant

Voyons maintenant la transformation dans 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

Notez que les lignes 61 à 67 sont les résultats de ces instructions à l’intérieur de la procédure.

La table de variables déclarées est transformée en table temporaire. Notez que le nom, qui dans le nom le caractère @ a été remplacé pour T_.

Si nous exécutons ce code dans Snowflake, nous n’obtiendrons aucun résultat. Il affichera simplement null. C’est parce que le dernier Select se trouve maintenant dans l’assistant EXEC. Alors, comment savoir si la table est là ?

Puisqu’elle a été créée en tant que table temporaire dans la procédure dans EXEC, nous pouvons effectuer une sélection dans cette table en dehors de la procédure.

 Select * from PUBLIC.T_VariableNameTable;
Copy

Si nous exécutons cette instruction, nous obtiendrons le résultat suivant

SET @Variable

Pour l’instant, la variable Set est transformée en fonction de l’expression qu’elle contient dans sa partie droite.

Si l’expression a une transformation, elle sera transformée en son équivalent JavaScript.

Exemple

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

Code traduit

 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

Comme vous pouvez le voir dans l’exemple, la valeur de la variable NOTSUPPORTED est commentée puisqu’elle n’est pas transformée pour le moment. Cela signifie que la transformation n’est pas encore terminée.

D’autres types d’ensembles sont commentés, par exemple les suivants

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

Code traduit

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

Pour l’instant, SELECT @variable est transformé en un simple select, en supprimant les assignations de variables et en conservant les expressions à droite de l’opérateur. Les opérations d’affectation des variables locales dans select seront remplacées par des fonctions arrow qui représentent le même comportement que l’opération effectuée lors de l’affectation de la variable locale dans SQL Server.

Entrée
 -- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1 AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
SELECT @VAR1 = COL1 + COL2, @VAR2 = COL3 FROM TABLE1;
GO
Copy
Sortie
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

Forme de base

La forme de base SELECT n’a pas de liaisons, la traduction implique donc la création d’un appel à la fonction d’assistance EXEC, avec un paramètre.\ \ Par exemple :

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

IF

Code source

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

Code traduit

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

WHILE

Code source

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

Code traduit

 while ( Conditional_Expression )
{
  // SQL STATEMENTS
}
Copy

EXEC / EXECUTE

Code source

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

Code traduit

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

La transformation pour THROW garantit que le bloc catch qui reçoit l’erreur a accès aux informations spécifiées dans l’instruction d’origine.

Par exemple :

 -- Case 1
THROW

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

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

Sera transformé en :

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

La fonction RAISERROR SQL Server n’est pas prise en charge par Snowflake. SnowConvert identifie toutes les utilisations afin de générer un assistant qui émule le comportement original. Exemple :

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

La transformation break/continue permet d’arrêter le flux du code ou de le poursuivre avec un autre bloc.

Par exemple :

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

Sera transformé en :

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

Le code est légèrement modifié car l’instruction INSERT INTO [Table] EXEC(...) n’est pas prise en charge par Snowflake. Ceci nous permet de répliquer le comportement en ajoutant quelques lignes de code :

  • La première ligne ajoutée est un appel à insertIntoTemporaryTable où le code extrait de l’argument se trouve à l’intérieur de EXEC, ce qui permet d’insérer le jeu de résultats dans une table temporaire. Pour plus d’informations sur cette fonction, consultez la section Assistant Into EXEC.

  • L’Insert de Exec est supprimé du code et une requête récupère les résultats de EXEC dans la table temporaire.

     SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable
    
Copy
  • La dernière ligne ajoutée est une instruction DROP TABLE pour la table temporaire ajoutée.

     DROP TABLE SnowConvertPivotTemporaryTable
    
Copy

Code source :

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

INSERT INTO #Table1
EXEC (@DBTables);
Copy

Code traduit :

   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 est transformée en commande BEGIN de Snowflake et insérée dans un appel à l’assistant EXEC.

L’assistant est chargé de l’exécution effective de l’application BEGIN.

Exemple :

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

COMMIT TRANSACTION

COMMIT TRANSACTION est transformée en commande COMMIT de Snowflake et insérée dans un appel à l’assistant EXEC.

L’assistant est chargé de l’exécution effective de l’application COMMIT.

Exemple :

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

ROLLBACK TRANSACTION

ROLLBACK TRANSACTION est transformée en commande ROLLBACK de Snowflake et insérée dans un appel à l’assistant EXEC.

L’assistant est chargé de l’exécution effective de l’application ROLLBACK.

Exemple :

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

WAITFOR DELAY

La clause WAITFOR DELAY est transformée en fonction SYSTEM$WAIT de Snowflake. Le paramètre time_to_pass de DELAY est transformé en secondes, pour utilisation comme paramètre dans la fonction SYSTEM$WAIT.

Les autres variantes de la clause WAITFOR ne sont pas prises en charge par Snowflake et sont donc signalées par le message correspondant.

Exemple :

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

Comme CURSORS n’est pas pris en charge par Snowflake, SnowConvert mappe leur fonctionnalité sur une aide JavaScript qui émule le comportement original dans la plateforme cible. Exemple :

Entrée :

 -- 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
Sortie :
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

Pour l’instant, le curseur Declare est simplement commenté.

Code source

 DECLARE myCursor1 CURSOR FOR SELECT COL1 FROM TABLE1
Copy

Code traduit

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

OPEN

Code source

 OPEN myCursor1
OPEN GLOBAL myCursor2
Copy

Code traduit

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

FETCH

Code source

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

Code traduit

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

CLOSE

Code source

 CLOSE myCursor1
CLOSE GLOBAL myCursor2
Copy

Code traduit

 myCursor1.CLOSE()
myCursor2.CLOSE()
Copy

DEALLOCATE

Code source

 DEALLOCATE myCursor1
DEALLOCATE GLOBAL myCursor2
Copy

Code traduit

 myCursor1.DEALLOCATE()
myCursor2.DEALLOCATE()
Copy

@@FETCH_STATUS

Code source

 @@FETCH_STATUS
Copy

Code traduit

 myCursor1.FETCH_STATUS()
Copy

@@CURSOR_ROWS

Code source

 @@CURSOR_ROWS
Copy

Code traduit

 myCursor1.FETCH_STATUS()
Copy

4. Expressions

Opérations binaires

Code source

 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

Code traduit

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

Conditionnels

Code source

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

Code traduit

 VAR1 > 0
VAR1 = 0
VAR1 < 0
VAR1 != 0
Copy
Prédicat IN
Prédicat NULL

Code source

 @var1 is null
@var2 is not null
Copy

Code traduit

 VAR1 == null
VAR2 != null
Copy

5. Labels and Goto

Les étiquettes n’ont pas le même comportement dans JavaScript que dans SQL Server. Pour simuler le comportement, elles sont transformées en fonctions. Son utilisation est remplacée par un appel à la fonction générée qui contient toute la logique de l’étiquette. Exemple :

Code source

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

ERROR_EXIT:
	RETURN @ErrorStatus
Copy
Code traduit
 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

Comme vous le voyez dans l’exemple ci-dessus, les déclarations de fonctions qui étaient les étiquettes dans le code source, seront placées à la fin du code afin de le rendre plus propre.

GOTO est une autre commande qui n’existe pas sur JavaScript. Pour simuler son comportement, leurs utilisations sont transformées en appels à la fonction (label) qui est référencée, précédés d’une instruction de retour. Exemple :

Code source
 -- 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
Code traduit
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

Comme vous le voyez dans l’exemple ci-dessus, le retour est ajouté à l’appel de la fonction, afin d’arrêter le flux de code comme le fait SQL Server avec GOTO.

Problèmes connus

Aucun problème n’a été constaté.

EWIs connexes

  1. SSC-EWI-0040 : Instruction non prise en charge.

  2. SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.

Tables

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Create table de base

Source

 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

Valeur attendue

 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

Tables temporaires

Dans le code source, certains noms de tables peuvent commencer par le caractère #.

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

Si c’est le cas, ils sont transformés en tables temporaires dans le code de sortie.

Voyons comment le code ci-dessus serait migré.

 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

Comme vous pouvez le voir, TEMPORARY a été ajouté à la définition de la table, et le caractère # a été remplacé par T_.

De même, toutes les références de la table seront également transformées pour correspondre au nouveau nom donné à la table temporaire.

Option de colonne NULL et NOT NULL

Les options de colonne NULL et NOT NULL sont prises en charge dans Snowflake.

Source

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

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

Valeur attendue

 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

Option de colonne d’identité

Pour les colonnes d’identité, une séquence est créée et attribuée à la colonne.

Source

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

Valeur attendue

 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

Option de colonne par défaut

L’expression par défaut est prise en charge dans Snowflake, cependant, dans Sql Server, elle peut être associée à une contrainte Nom. Cette partie n’étant pas prise en charge par Snowflake, elle a été supprimée et un avertissement a été ajouté.

Source

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

Valeur attendue

 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

Contrainte de colonne

Source

 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

Valeur attendue

 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

Option de colonne Collate

Pour la transformation Collate, veuillez consulter le lien suivant.

collate.md

Option de colonne ENCRYPTED WITH

L’option Encrypted With n’est pas prise en charge dans Snowflake, elle est donc supprimée et un avertissement est ajouté.

Source

 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

Valeur attendue

 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

L’option NOT FOR REPLICATION n’est pas prise en charge dans Snowflake. Elle est utilisé pour l’identité qui est migrée vers SEQUENCE.

Avertissement

Notez que NOT FOR REPLICATION est une instruction qui n’est pas requise dans Snowflake car elle est traduite par un équivalent, elle est donc supprimée.

Source

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

Sortie

 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

L’option ON PRIMARY est une instruction utilisée dans SQL Server pour définir sur quel fichier un objet, par exemple une table, va être créé. Par exemple, sur un groupe de fichiers primaire ou secondaire à l’intérieur de la base de données. Snowflake fournit une logique différente et indique des contraintes distinctes. Pour plus d’informations, veuillez consulter la documentation Snowflake suivante.

Source

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

Tri des colonnes ASC/DESC

Le tri des colonnes n’est pas pris en charge dans Snowflake, les mots-clés ASC ou DESC sont supprimés.

Source

 CREATE TABLE [TABLE1](
	[COL1] [int] NOT NULL,
 CONSTRAINT [constraint1] PRIMARY KEY CLUSTERED ([COL1] ASC)
) ON [PRIMARY]
Copy
Sortie
 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

Colonnes calculées

Les colonnes calculées sont prises en charge dans Snowflake, il suffit d’ajouter le type de données explicite pour pouvoir déployer la table, par exemple.

Source

 CREATE TABLE [TABLE1](
	[COL2] [int] NOT NULL,
	[COL2] [int] NOT NULL,
	[COL1] AS (COL3 * COL2),
)
Copy
Sortie
 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

Si l’expression calculée ne peut pas être transformée, un avertissement est ajouté et une simple définition de colonne avec le type de retour de l’expression sera utilisée à la place, comme dans l’exemple suivant :

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

L’expression CONVERT ([NUMERIC], ExpressionValue) n’est pas encore prise en charge par SnowConvert, donc, après son inspection, SnowConvert déterminera que son type est XML, et la transformation sera donc la suivante

 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

SnowConvert lancera un processus pour déterminer le type d’expression d’origine dans SQL Server. Toutefois, la colonne aura le type de cible équivalent. Dans l’exemple précédent, le type de colonne dans SQLServer était XML, mais le type de cible dans Snowflake pour stocker un XML est TEXT. Pour plus d’informations sur le mappage des types de données, consultez les sections sur les types de données.

Option de colonne MASKED WITH

Dans SQL Server, le masquage des données est utilisé pour empêcher les utilisateurs non privilégiés d’accéder à des informations sensibles. Consultez la documentation SQL SERVER pour plus d’informations. Dans Snowflake, il existe une fonctionnalité de masquage dynamique des données, mais elle n’est disponible que pour l’Enterprise Edition. Veuillez consulter la documentation Snowflake suivante.

Entrée

 CREATE TABLE TABLE1
(
	[COL1] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NULL
);
Copy
Sortie
 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

Option de colonne ROWGUIDCOL

ROWGUIDCOL n’est plus applicable dans Snowflake. Il est utilisé dans SQL Server pour les types UNIQUEIDENTIFIER qui sont actuellement traduits en VARCHAR. Par exemple :

Entrée

 CREATE TABLE TABLEROWID (
    [ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];
Copy
Sortie
 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

Option de colonne GENERATED ALWAYS AS ROW START/END

ROW START/END n’est pas pris en charge par Snowflake. Une erreur est ajoutée lorsque SnowConvert tente de transformer ce type d’option de colonne.

Entrée

 CREATE TABLE TABLEROWID (
    [COL1] DATETIME GENERATED ALWAYS AS ROW START NOT NULL
) ON [PRIMARY];
Copy
Sortie
 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

Problèmes connus

Aucun problème n’a été constaté.

EWIs connexes

  1. SSC-EWI-0036 : Type de données converti en un autre type de données.

  2. SSC-EWI-0040 : Instruction non prise en charge.

  3. SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.

  4. SSC-EWI-TS0017 : Le masquage n’est pas pris en charge.

  5. SSC-FDM-0012 : La contrainte dans l’expression par défaut n’est pas prise en charge.

  6. SSC-FDM-TS0002 : Ce message s’affiche lorsqu’une clause de classement n’est pas prise en charge par Snowflake.

  7. SSC-FDM-TS0009 : Encrypted with n’est pas pris en charge dans Snowflake.

  8. SSC-FDM-TS0014 : Colonne calculée transformée.

  9. SSC-FDM-TS0015 : Le type de données n’est pas pris en charge par Snowflake.

  10. SSC-PRF-0002 : Les colonnes insensibles à la casse peuvent diminuer les performances des requêtes.

Vues

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Dans cette section, nous allons vérifier la transformation pour Create View.

Modèles d’échantillons de sources

SIMPLE CREATE VIEW

L’exemple suivant montre une transformation pour une instruction CREATE VIEW simple.

 CREATE VIEW VIEWNAME
AS
SELECT AValue from ATable;
Copy

Valeur attendue

 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

La définition de CREATE OR ALTER utilisée dans SqlServer est transformée en CREATE OR REPLACE dans 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

Dans ce type de vue, après le nom de la vue, on peut trouver les clauses suivantes

  • WITH ENCRYPTION

  • WITH SCHEMABINDING

  • WITH VIEW_METADATA

Avertissement

Notez que les clauses ci-dessus sont supprimées de la traduction, parce qu’elles ne sont pas pertinentes dans la syntaxe 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

Dans ce type de vue, la clause WITH CHECK OPTION vient après la fin de l’instruction Select utilisée dans Create View.

Avertissement

Notez que WITH CHECK OPTIONa été supprimé de la traduction, car il n’est pas pertinent dans la syntaxe Snowflake.

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

Valeur attendue

 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

Des expressions de table communes doivent être utilisées pour récupérer les données :

 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

Les expressions de table courantes avec des instructions de mise à jour, d’insertion ou de suppression seront commentées car elles ne sont pas prises en charge par Snowflake et SQLServer.

Dans le cas où une CTE invalide est ajoutée à la vue, celle-ci sera complètement commentée.

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

Voyons un dernier échantillon, rassemblons tous les cas que nous avons vus jusqu’à présent et voyons comment se ferait la transformation

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

Valeur attendue

 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

Comme vous pouvez le voir, nous avons remplacé OR ALTER par OR REPLACE et nous avons supprimé la clause WITH ENCRYPTION qui vient après le nom de la vue et la clause WITH CHECK OPTION qui vient après le Select.

EWIs connexes

  1. SSC-PRF-TS0001 : Avertissement de performance - la récursivité pour CTE n’a pas été vérifiée. Peut nécessiter un mot-clé récursif.

Azure Synapse Analytics

Applies to
  • [x] Azure Synapse Analytics

Description

Cette section présente la traduction de la syntaxe spécifique aux tables Azure Synapse Analytics.

Note

Pour la plupart des modèles, consultez la spécification de traduction pour les tables générales, car elles partagent pratiquement la même syntaxe et le même comportement.

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

Modèles d’échantillons de sources

WITH options de table

Azure Synapse Analytics présente une syntaxe supplémentaire pour définir les options de table.

 <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

Snowflake gère automatiquement l’optimisation des tables grâce à des mécanismes tels que la micropartition. C’est pourquoi il n’existe pas de syntaxe équivalente pour certaines de ces options de table dans Snowflake. Il n’est donc pas nécessaire de définir certaines options de table de Transact.

Options de table qui seront omises :

  • CLUSTERED COLUMNSTORE INDEX (sans colonne)

  • HEAP

  • DISTRIBUTION

  • PARTITION

CLUSTERED [ COLUMNSTORE ] INDEX avec des colonnes, sera transformé en CLUSTER BY dans Snowflake . Un examen des performances PRF sera ajouté car il est conseillé de vérifier si la définition d’un CLUSTER KEY est nécessaire.

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

Problèmes connus

Aucun problème n’a été constaté.

EWIs connexes

  1. SSC-PRF-0007 : PERFORMANCE REVIEW - CLUSTER BY.

TEXTIMAGE_ON

Applies to
  • [x] SQL Server

Note

Instruction non pertinente.

Avertissement

Remarquez que cette instruction est retirée de la migration parce qu’il s’agit d’une syntaxe non pertinente. Cela signifie qu’elle n’est pas exigée dans Snowflake.

Description

TEXTIMAGE_ON [PRIMARY] est un moyen, dans Transact, de traiter les grands groupes d’informations à l’intérieur d’une table. Dans Snowflake, il n’est pas nécessaire de définir ce type de caractéristiques, car Snowflake traite les fichiers de données ou les informations de grande taille d’une manière différente.

Modèles d’échantillons de sources

Notez que dans cet exemple, TEXTIMAGE_ON [PRIMARY] a été supprimé en raison de sa syntaxe inutile.

 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

Problèmes connus

Aucun problème n’a été constaté.

EWIs connexes

Pas d’EWIs connexes.

FUNCTION

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Description

SQL Server ne prend en charge que deux types de fonctions définies par l’utilisateur :

À partir de ces types d’UDFs, il est possible de les sous-catégoriser en simples et complexes, selon la logique interne.

Les UDFs simples ; ce type fait correspondre la syntaxe SQL Server à la syntaxe de Snowflake. Ce type n’ajoute aucune logique et va directement au résultat. Ils correspondent généralement aux UDFs SQL de Snowflake.\ \ Les UDFs complexes ; ce type fait un usage intensif d’une instruction particulière (INSERT, DELETE, UPDATE, SET, DECLARE, etc) ou de blocs de contrôle du flux blocs(IF…ELSE, WHILE, etc) et représente généralement une inadéquation ou une violation de la définition des UDFs SQL de Snowflake.

Limitations

Les UDFs Transact présentent certaines limites qui ne sont pas présentes dans d’autres moteurs de base de données (comme Oracle et Teradata). Ces limites facilitent les traductions en réduisant le champ d’application de l’échec. Cela signifie qu’il existe des scénarios spécifiques que nous pouvons espérer éviter.

Voici quelques-unes des limites de SQL Server applicables aux UDFs

  • Les UDFs ne peuvent pas être utilisées pour effectuer des actions qui modifient l’état de la base de données

  • Les fonctions définies par l’utilisateur ne peuvent pas contenir de clause OUTPUT INTO ayant une table pour cible

  • Les fonctions définies par l’utilisateur ne peuvent pas renvoyer plusieurs jeux de résultats. Utilisez une procédure stockée si vous devez renvoyer plusieurs jeux de résultats.

Pour obtenir la liste complète, consultez le lien Créer des fonctions définies par l’utilisateur (moteur de base de données)

scalaire.md

inline-table-valued.md

INLINE TABLE-VALUED

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Description

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Les fonctions en ligne à valeur de table sont des expressions de table qui peuvent accepter des paramètres, exécuter une instruction SELECT et renvoyer une TABLE (Référence linguistique SQL Server Création d’une fonction à valeur de table intégrée.)

Syntaxe 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

Syntaxe 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

Modèles d’échantillons de sources

La section suivante décrit tous les modèles de code source pouvant apparaître dans ce type de syntaxe CREATE FUNCTION.

Pour les fonctions en ligne à valeur de table, il ne peut y avoir qu’une seule instruction par corps qui puisse être :

  • Instruction SELECT

  • Expression de table commune WITH

Sélectionner et retourner des valeurs directement à partir d’une table

Il s’agit du scénario le plus simple, qui consiste à effectuer une simple sélection dans une table et à renvoyer les valeurs correspondantes

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

Sélectionner et retourner des valeurs à partir de plusieurs tables en renommant les colonnes et en utilisant des fonctions intégrées

Voici un exemple de requête utilisant des fonctions intégrées dans une instruction Select pour obtenir des données de différentes tables, renommer les colonnes et renvoyer une table.

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

Sélectionnez des colonnes à l’aide de l’instruction WITH

Le corps d’une fonction à valeur de table intégrée peut également être spécifié à l’aide d’une instruction WITH, comme indiqué ci-dessous.

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

Problèmes connus

Aucun problème n’a été trouvé

EWIs connexes

  1. SSC-FDM-TS0012 : L’information pour l’expression n’a pas été trouvée. CAST vers STRING utilisé

  2. SSC-PRF-TS0001 : Avertissement de performance - la récursivité pour CTE n’a pas été vérifiée. Peut nécessiter un mot-clé récursif.

  3. SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle

MULTI-STATEMENT TABLE-VALUED

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Note

Tous les échantillons de code figurant sur cette page n’ont pas encore été mis en œuvre dans SnowConvert. Ils doivent être interprétés comme une référence pour la manière dont chaque scénario doit être traduit dans Snowflake. Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

Une fonction à valeur de table multi-instructions est similaire à une fonction à valeur de table d’instruction intégrée. (INLINE TABLE-VALUED. Cependant, la fonction à valeur de table multi-instructions peut avoir plus d’une instruction dans son corps, les colonnes de la table sont spécifiées dans le type de retour et elle a un bloc BEGIN/END (Référence linguistique SQL Server Créer une fonction à valeur de table multi-instructions

Syntaxe 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

Modèles d’échantillons de sources

La section suivante décrit tous les modèles de code source pouvant apparaître dans ce type de syntaxe CREATE FUNCTION.

Le corps de la fonction à valeur de table multi-instructions doit être une instruction SELECT. C’est pourquoi les autres instructions doivent être appelées séparément.

Insérer des valeurs dans une table

Insère une ou plusieurs lignes dans la table et renvoie la table avec les nouvelles valeurs

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

Insérer une valeur en fonction de l’instruction if/else

Insère une ligne dans la table conformément à la condition et renvoie la table avec la nouvelle valeur

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

Insère plusieurs éléments en fonction de l’instruction if/else

L’exemple ci-dessous insère plusieurs valeurs dans la table et plusieurs variables sont modifiées en fonction de la condition. Renvoie la table contenant les nouvelles valeurs

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

Avertissement

Dans le cas d’instructions if imbriquées et si plusieurs variables sont modifiées dans les instructions, il est nécessaire d’utiliser une procédure stockée.

Mise à jour des valeurs précédemment insérées

Met à jour les valeurs des colonnes de la table dans le corps de la fonction et la renvoie avec les nouvelles valeurs.

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

Clauses de retour multiples

Dans l’échantillon suivant, il y a plus d’une clause de retour, car selon la situation, il n’est pas nécessaire de continuer à exécuter toute la fonction.

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

Avertissement

Cette transformation est appliquée lorsqu’il n’y a qu’une seule valeur à insérer. S’il y a plus d’une valeur, il est nécessaire d’utiliser une procédure stockée.

Cas complexes

L’exemple est un casse complexe qui utilise des instructions imbriquées if et insère une valeur en fonction de la condition true.

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

Problèmes connus

Instructions While avec requêtes en parallèle

Le problème de cet exemple est qu’il n’y a aucun moyen de transformer l’instruction while en CTE à l’intérieur de la clause WITH du select principal, ce qui nous oblige à transformer cette instruction en procédure stockée pour conserver la même logique.

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

Déclarer le curseur

Les fonctions définies par l’utilisateur ne peuvent pas DECLARE, OPEN, FETCH, CLOSE ou DEALLOCATE a CURSOR. Utilisez une procédure stockée pour travailler avec des curseurs.

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

Les instructions différentes ne sont pas prises en charge dans les expressions de tables communes

Les clauses UPDATE, INSERT, DELETE, ALTER ou DROP ne sont pas prises en charge dans le corps des expressions de tables communes, même après leur déclaration à l’aide d’un délimiteur. C’est pourquoi la fonction peut être modifiée pour fonctionner comme une procédure stockée.

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 connexes

  1. SSC-EWI-0040 : Instruction non prise en charge.

  2. SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle

SCALAR

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Description

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Une fonction définie par l’utilisateur est une routine Transact-SQL ou Common Language Runtime (CLR) qui accepte des paramètres, effectue une action, telle qu’un calcul complexe, et renvoie le résultat de cette action sous la forme d’une valeur scalaire. (Référence linguistique SQL Server Sous section CREATE FUNCTION).

Note

Ces fonctions sont généralement utilisées dans l’instruction SELECT ou dans la configuration d’une seule variable (très probablement dans une procédure stockée).

Syntaxe 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

Syntaxe Snowflake

Snowflake permet l’utilisation de 3 langues différentes dans les fonctions définies par l’utilisateur :

  • SQL

  • JavaScript

  • Java

Pour l’instant, SnowConvert ne prend en charge que les langues cibles SQL et JavaScript.

Note

Les fonctions définies par l’utilisateur SQL n’acceptent qu’une seule requête comme corps. Elles peuvent lire la base de données, mais ne sont pas autorisées à l’écrire ou à la modifier. (Référence des UDFs SQL scalaires).

 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

Note

Les fonctions définies par l’utilisateur JavaScript autorisent plusieurs instructions dans leur corps, mais ne peuvent pas effectuer de requêtes dans la base de données. (Référence des UDFs JavaScript scalaires)

 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

Modèles d’échantillons de sources

Instructions Set et Declare

Les instructions les plus courantes dans les corps de fonctions sont les instructions DECLARE et SET. Pour les instructions DECLARE sans valeur par défaut, la transformation sera ignorée. Les instructions SET et DECLARE avec une valeur par défaut seront transformées en COMMON TABLE EXPRESSION. Chaque expression de table commune contiendra une colonne qui représente la valeur de la variable locale.

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

Transformation des instructions If/Else

Les instructions If/Else peuvent être traitées de différentes manières, elles peuvent être transformées en Javascript ou en SQL à l’aide de CASE EXPRESSION dans Select qui permet des conditionnels à l’intérieur des requêtes, alors que la transformation en Javascript est assez simple, l’instruction Case peut ne pas être si évidente à première vue.

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

Instructions imbriquées

Pour les instructions imbriquées, la programmation structurée est transformée en une requête unique. Les instructions du contrôle du flux seront imbriquées dans des structures de table afin de préserver l’ordre d’exécution.

Note

CASE EXPRESSIONS ne peut renvoyer qu’une seule valeur par instruction

Exemple

Note

Le code suivant est fonctionnellement équivalent dans les deux paradigmes de programmation.

 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

Variables conditionnelles via SELECTs

La définition et l’affectation de variables dans les instructions conditionnelles posent généralement des problèmes, car les références à la variable plus loin dans le code doivent savoir où la variable a été modifiée pour la dernière fois. De plus, si la référence se trouve à l’intérieur d’une autre instruction conditionnelle, il faudrait une sorte de redirection qui fasse référence à l’affectation précédente connue de la variable.

Tout cela est aggravé par l’imbrication et la complexité des requêtes que l’on peut trouver sur le code d’entrée.

Dans le scénario suivant, la première instruction IF peut être transformée sans problème, car son contenu est assez simple. Les deuxième et troisième instructions IF sont commentées parce qu’elles ne sont pas prises en charge pour le moment, étant donné qu’il existe des instructions autres que les affectations de variables par le biais 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

Assigner et retourner une variable

Dans ce modèle simple, il y a une déclaration de variable, puis cette variable est définie à l’aide d’une instruction SELECT et enfin renvoyée. Celle-ci va être migrée vers une expression de table commune afin de conserver le comportement d’origine.

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

Appels de fonctions multiples

Pour ce modèle spécifique, il n’y a pas de requêtes évidentes, mais il y a plusieurs appels à plusieurs fonctions travaillant sur la même variable et la renvoyant à la fin. Comme Snowflake ne prend en charge que les requêtes à l’intérieur de ses fonctions, la solution pour ce bloc va être de l’ajouter à un Select et d’imbriquer les appels à l’intérieur, en s’assurant que la valeur de retour est la même que celle de la source.

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

Augmenter une variable en fonction de plusieurs conditions IF et renvoyer sa valeur

Pour ce modèle, une variable est modifiée (augmentée dans le cas présent) à l’aide de plusieurs conditions IF. Au début, un jeu de résultats est initialisé et utilisé pour déterminer si la variable de résultat doit être augmentée ou non. Enfin, la variable de résultat est renvoyée.

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

Deux ou plusieurs instructions RETURN

Pour ce modèle, le bloc IF contenant la clause de retour qui interrompt le flux de code est ajouté à la fin du corps, comme la dernière instruction à exécuter dans une expression CASE.

Cas de base

Pour ce scénario particulier, il n’y a pas de logique entre l’instruction conditionnelle RETURN et l’instruction finale RETURN, de sorte que tous les corps seront mappés sur une seule 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

Expressions de table communes

Les expressions de table communes seront conservées comme dans le code original, et elles seront concaténées avec les expressions générées. SnowConvert est capable d’identifier en premier lieu tous les noms originaux de COMMON TABLE EXPRESSION afin d’éviter de générer des noms dupliqués.

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

Transformer en UDFs JavaScript

S’il y a plusieurs instructions et que la fonction n’accède en aucune façon à la base de données, elle peut être transformée en une fonction JavaScript en conservant l’équivalence fonctionnelle

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

Problèmes connus

Avertissement

Les fonctions définies par l’utilisateur ne peuvent pas être utilisées pour effectuer des actions qui modifient l’état de la base de données

Avertissement

Les fonctions définies par l’utilisateur ne peuvent pas contenir de clause OUTPUT INTO ayant une table pour cible

Avertissement

Les fonctions définies par l’utilisateur ne peuvent pas DECLARE, OPEN, FETCH, CLOSE ou DEALLOCATE a CURSOR. Utilisez une procédure stockée si vous devez utiliser des curseurs.

Avertissement

Les fonctions définies par l’utilisateur ne peuvent pas exécuter d’instructions de contrôle du flux, telles que WHILE, s’il y a au moins un appel à la base de données

Avertissement

Les fonctions définies par l’utilisateur qui contiennent des références à d’autres fonctions définies par l’utilisateur et qui ont été transformées en procédures stockées seront également transformées en procédures stockées.

Avertissement

Les fonctions définies par l’utilisateur qui utilisent @@ROWCOUNT ne sont pas prises en charge dans SQL et doivent être transformées en procédures stockées afin de conserver l’équivalence fonctionnelle.

Avertissement

Les fonctions définies par l’utilisateur qui ont des instructions SELECT assignant une variable à elle-même ne sont pas prises en charge dans Snowflake. Voir aussi SELECT @local_variable

Pour tous les cas non pris en charge, veuillez consulter les EWIs connexes et les modèles ci-dessous pour obtenir des recommandations et des solutions de contournement.

Conditionnelles autres que les instructions if/else en parallèle des requêtes

Le scénario suivant implique l’utilisation de l’instruction « while » en parallèle d’autres requêtes. Le problème de cet exemple est qu’il n’y a aucun moyen de transformer l’instruction while en CTE à l’intérieur de la clause WITH du select principal, ce qui nous oblige à transformer cette instruction en procédure JavaScript pour conserver la même logique.

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

Affectation d’une variable à l’aide de sa propre valeur en parcourant un jeu de lignes

Dans l’exemple suivant, la variable @names est utilisée pour concaténer plusieurs valeurs d’une colonne en une seule chaîne. La variable est mise à jour à chaque itération comme indiqué, ce qui n’est pas pris en charge par les UDFs SnowFlake. Pour ce scénario, la fonction doit être transformée en procédure.

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

Requête 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

Avertissement

Pour les scénarios décrits ci-dessus, tenez compte des limites suivantes :

  1. Tous les appels aux fonctions définies par l’utilisateur dans les requêtes DML telles que SELECT, INSERT, DELETE, UPDATE ou MERGE échoueront parce que les appels aux procédures stockées dans ces requêtes ne sont pas autorisés.

  2. Les appels à des fonctions définies par l’utilisateur à l’intérieur de procédures doivent être précédés du mot-clé CALL.

  3. Les fonctions définies par l’usage utilisées dans les COMPUTED COLUMNS échoueront lors de l’exécution.

EWIs connexes

  1. SSC-EWI-0067 : UDF a été transformée en procédure Snowflake, l’appel de procédures à l’intérieur d’une requête n’est pas pris en charge.

  2. SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.

  3. SSC-FDM-0029 : Une fonction définie par l’utilisateur a été transformée en procédure Snowflake.