Links

SQL Server

SQL Server Unsupported Cases

1. Parameters Optional Arguments

In Transact-SQL exists several parameters options that are not supported by Snowflake Scripting such as READONLY, VARYING and OUTPUT.

Input Code:

CREATE OR ALTER PROCEDURE SampleProcedure
@Param1 INT READONLY,
@Param2 INT VARYING,
@Param3 INT OUTPUT
AS
BEGIN
-- Procedure body
END

Output Code:

CREATE OR REPLACE PROCEDURE GetVacationHours (
PARAM1 FLOAT /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'READONLY PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/,
PARAM2 FLOAT /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'VARYING PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/,
PARAM3 FLOAT /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'OUTPUT PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLE
AS
$$
BEGIN
-- Procedure Body
END;
$$;

2. Create Procedure Optional Arguments

In Transact-SQL exists several Create Procedure options that are not supported by Snowflake Scripting such as WITH ENCRYPTION, WITH RECOMPILE or FOR REPLICATION.

Input Code:

CREATE OR ALTER PROCEDURE SampleProcedure
WITH ENCRYPTION, RECOMPILE, EXECUTE AS 'user_name'
FOR REPLICATION
AS
BEGIN
-- Procedure body
END

Output Code:

CREATE OR REPLACE PROCEDURE GetVacationHours ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
/*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'ENCRYPION OPTION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
/*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'RECOMPILE OPTION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
/*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'EXECUTE AS USER_NAME' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
/*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'FOR REPLICATION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
AS
$$
BEGIN
-- Procedure Body
END;
$$;

3. SET Property and Mutator

Input Code:

CREATE OR ALTER PROCEDURE customData
AS
SET @p.X = @p.X + 1.1;
SET @p.SetXY(22, 23);
RETURN Point;
GO

Output Code:

CREATE OR REPLACE PROCEDURE customData ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'SET PROPERTY AND SET MUTATOR' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
-- SET @p.X = @p.X + 1.1;
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'SET PROPERTY AND SET MUTATOR' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
-- SET @p.SetXY
END;
$$;

4. Deallocate

The cursor deallocate is not supported by Snowflake Scripting.

Input Code:

CREATE OR ALTER PROCEDURE CursorExample
AS
DECLARE
@CursorVar CURSOR;
DEALLOCATE @CursorVar;
GO

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.CursorExample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
-- ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR STATEMENTS IS PLANNED TO BE DELIVERED IN THE FUTURE **
-- DECLARE
-- @CursorVar CURSOR;
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'DEALLOCATE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
-- DEALLOCATE @CursorVar
;
END;
$$;
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO

Last modified 10mo ago