SCALAR

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

Description

Some parts in the output code are omitted for clarity reasons.

A scalar user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a scalar value. (SQL Server Language Reference CREATE FUNCTION subsection).

These functions are usually used inside the SELECTstatement, or single variable setup (most likely inside a stored procedure).

SQL Server Syntax

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

Snowflake Syntax

Snowflake allows 3 different languages in their user defined functions:

• SQL

• JavaScript

• Java

For now, SnowConvert will support only SQL and JavaScript as target languages.

SQL

SQL user defined functions only supports one query as their body. They can read from the database, but is not allowed to write or modify it. (Scalar SQL UDFs Reference).

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

JavaScript

JavaScript user defined functions allows multiple statements in their bodies, but cannot perform queries to the database. (Scalar JavaScript UDFs Reference)

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

Sample Source Patterns

Set and Declare Statements

The most common statements in function bodies are the DECLARE and SET statements. For DECLARE statements without default value, the transformation will be ignored. SET statements and DECLARE statements with a default value, will be transformed to a COMMON TABLE EXPRESSION. Each common table expression will contain a column that represents the local variable value.

SQL Server

Query

IN -> SqlServer_01.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;

Result

vendor_name            | 
-----------------------+ 
Australia Bike Retailer|

Snowflake

Query

OUT -> SqlServer_01.sql

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;

Result

VENDOR_NAME            |
-----------------------+
Australia Bike Retailer|

If/Else Statement Transformation

If/Else statement can be handled in different ways, they can be either transformed to javascript or to SQL using the CASE EXPRESSION inside the select allowing conditionals inside the queries, while the javascript transformation is pretty straightforward, the Case statement might not be so obvious at first glance.

SQL Server

Query

IN -> SqlServer_02.sql

CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
	DECLARE @result VARCHAR(10)
	DECLARE @ActiveFlag BIT