DROPs

Translation reference to convert SQL Server DROP FUNCTION and DROP PROCEDURE to Snowflake Scripting

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

Description

To drop a procedure or function in Snowflake SQL it is necessary to specify the parameters types in the drop statement, which is the main difference with SQL Server. In this translation, IF EXISTS clause is added by default to avoid runtime errors in Snowflake SQL.

Sample Source Patterns

The following examples details the drop routine statement for procedures and functions.

SQL Server

Drop Routine

IN -> SqlServer_01.sql

CREATE PROCEDURE SOMEPROCEDURE()
AS
BEGIN
    DROP FUNCTION SOMEFUNCTIONTODROP;
    DROP PROCEDURE IF EXISTS SOMEPROCEDURETODROP;
END;

Snowflake SQL

Drop Routine

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE SOMEPROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DROP FUNCTION IF EXISTS SOMEFUNCTIONTODROP ();
        DROP PROCEDURE IF EXISTS SOMEPROCEDURETODROP ();
    END;
$$;

Known Issues

No issues were found.

Related EWIs

1. SSC-EWI-0073: Pending Functional Equivalence Review.