SQL Server

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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