SnowConvert AI - SQL Server-Azure Synapse - CREATE PROCEDURE(Snowflake Scripting)¶

CREATE PROCEDURE TestTransaction
AS
BEGIN
    DROP TABLE IF EXISTS NEWTABLE;
    CREATE TABLE NEWTABLE(COL1 INT, COL2 VARCHAR);
      BEGIN TRANSACTION;
         INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
         INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
      COMMIT TRANSACTION;
END

CREATE PROCEDURE TestTransaction
AS
BEGIN
    DROP TABLE IF EXISTS NEWTABLE;
    CREATE TABLE NEWTABLE(COL1 INT, COL2 VARCHAR);
      BEGIN TRANSACTION LabelA;
        INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
        INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
      COMMIT TRANSACTION LabelA;
END

CREATE OR REPLACE PROCEDURE TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DROP TABLE IF EXISTS NEWTABLE;
        CREATE OR REPLACE TABLE NEWTABLE (
            COL1 INT,
            COL2 VARCHAR
        );
            BEGIN TRANSACTION;
            INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
         INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
            COMMIT;
    END;
$$;

 CREATE OR REPLACE PROCEDURE TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DROP TABLE IF EXISTS NEWTABLE;
        CREATE OR REPLACE TABLE NEWTABLE (
            COL1 INT,
            COL2 VARCHAR
        );
            BEGIN TRANSACTION
            !!!RESOLVE EWI!!! /*** SSC-EWI-0101 - COMMENTED OUT TRANSACTION LABEL NAME BECAUSE IS NOT APPLICABLE IN SNOWFLAKE ***/!!!
            LabelA;
            INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
        INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
            COMMIT;
    END;
$$;

CREATE OR ALTER PROCEDURE SelectCaseDemoProcedure
AS
      SELECT TOP 10
          LOGINID,
          CASE (MARITALSTATUS)
              WHEN 'S' THEN 'SINGLE'
              WHEN 'M' THEN 'MARIED'
              ELSE 'OTHER'
          END AS status
      FROM HUMANRESOURCES.EMPLOYEE;
GO

EXEC SelectCaseDemoProcedure;

CREATE OR ALTER PROCEDURE SelectCaseDemoProcedure
AS
      SELECT TOP 10
          LOGINID,
          CASE
              WHEN MARITALSTATUS = 'S' THEN 'SINGLE'
              WHEN MARITALSTATUS = 'M' THEN 'MARIED'
              ELSE 'OTHER'
          END AS status
      FROM HUMANRESOURCES.EMPLOYEE;
GO

EXEC SelectCaseDemoProcedure;

CREATE OR REPLACE PROCEDURE SelectCaseDemoProcedure ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
      DECLARE
            ProcedureResultSet RESULTSET;
      BEGIN
            ProcedureResultSet := (
            SELECT TOP 10
                  LOGINID,
                CASE (MARITALSTATUS)
                    WHEN 'S' THEN 'SINGLE'
                    WHEN 'M' THEN 'MARIED'
                    ELSE 'OTHER'
                END AS status
            FROM
                  HUMANRESOURCES.EMPLOYEE);
            RETURN TABLE(ProcedureResultSet);
      END;
$$;

CALL SelectCaseDemoProcedure();

CREATE OR REPLACE PROCEDURE SelectCaseDemoProcedure ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
      DECLARE
            ProcedureResultSet RESULTSET;
      BEGIN
            ProcedureResultSet := (
            SELECT TOP 10
                  LOGINID,
                CASE
                    WHEN MARITALSTATUS = 'S' THEN 'SINGLE'
                    WHEN MARITALSTATUS = 'M' THEN 'MARIED'
                    ELSE 'OTHER'
                END AS status
            FROM
                  HUMANRESOURCES.EMPLOYEE);
            RETURN TABLE(ProcedureResultSet);
      END;
$$;

CALL SelectCaseDemoProcedure();

CREATE OR ALTER PROCEDURE SetCaseDemoProcedure
AS
    DECLARE @value INT;
    DECLARE @result INT;
    SET @value = 5;
    
    SET @result =
        CASE @value
            WHEN 1 THEN @value * 10
            WHEN 3 THEN @value * 20
            WHEN 5 THEN @value * 30
            WHEN 7 THEN @value * 40
            ELSE -1
        END;
    
    RETURN @result
GO

DECLARE @result INT;
EXEC @result = SetCaseDemoProcedure;
PRINT @result;

CREATE OR ALTER PROCEDURE SetCaseDemoProcedure
AS
    DECLARE @value INT;
    DECLARE @result INT;
    SET @value = 5;
    
    SET @result =
        CASE
            WHEN @value = 1 THEN @value * 10
            WHEN @value = 3 THEN @value * 20
            WHEN @value = 5 THEN @value * 30
            WHEN @value = 7 THEN @value * 40
            ELSE -1
        END;
    
    RETURN @result
GO

DECLARE @result INT;
EXEC @result = SetCaseDemoProcedure;
PRINT @result;

CREATE OR REPLACE PROCEDURE SetCaseDemoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VALUE INT;
        RESULT INT;
    BEGIN
         
         
        VALUE := 5;
        CASE (:VALUE)
            WHEN 1 THEN
                RESULT := :VALUE * 10;
            WHEN 3 THEN
                RESULT := :VALUE * 20;
            WHEN 5 THEN
                RESULT := :VALUE * 30;
            WHEN 7 THEN
                RESULT := :VALUE * 40;
            ELSE
                RESULT := -1;
        END;
        RETURN :RESULT;
    END;
$$;

DECLARE
    RESULT INT;
BEGIN
    CALL SetCaseDemoProcedure();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
    PRINT @result;
END;

CREATE OR REPLACE PROCEDURE SetCaseDemoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VALUE INT;
        RESULT INT;
    BEGIN
         
         
        VALUE := 5;
        CASE
            WHEN :VALUE = 1 THEN
                RESULT := :VALUE * 10;
            WHEN :VALUE = 3 THEN
                RESULT := :VALUE * 20;
            WHEN :VALUE = 5 THEN
                RESULT := :VALUE * 30;
            WHEN :VALUE = 7 THEN
                RESULT := :VALUE * 40;
            ELSE
                RESULT := -1;
        END;
        RETURN :RESULT;
    END;
$$;

DECLARE
    RESULT INT;
BEGIN
    CALL SetCaseDemoProcedure();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
    PRINT @result;
END;

CREATE PROC SampleProcedure AS;

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

CREATE OR ALTER PROCEDURE Add_Privacy_Department
AS
EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');

CREATE OR REPLACE PROCEDURE Add_Privacy_Department ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE 'INSERT INTO HumanResources.Department VALUES ('Privacy', 'Executive General and Administration', default);';
  END;
$$;

ALTER PROCEDURE procedureName
AS
SELECT 1 AS ThisDB;

CREATE OR REPLACE PROCEDURE procedureName ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
ProcedureResultSet RESULTSET;
BEGIN
ProcedureResultSet := (
SELECT 1 AS ThisDB);
RETURN TABLE(ProcedureResultSet);
END;
$$;

CREATE OR ALTER PROCEDURE SetNewPrice @ProductID INT, @NewPrice MONEY
AS
  BEGIN
    DECLARE @dynSqlStatement AS VARCHAR(300);
    SET @dynSqlStatement = 'UPDATE Production.ProductListPriceHistory SET ListPrice = ' + CAST(@NewPrice AS VARCHAR(10)) + ' WHERE ProductID = ' + CAST(@ProductID AS VARCHAR(10)) + ' AND EndDate IS NULL';
    EXECUTE (@dynSqlStatement);
  END;

CREATE OR REPLACE PROCEDURE SetNewPrice (PRODUCTID INT, NEWPRICE NUMBER(38, 4))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    DYNSQLSTATEMENT VARCHAR(300);
  BEGIN
     
    DYNSQLSTATEMENT := 'UPDATE Production.ProductListPriceHistory
   SET
      ListPrice = ' || CAST(:NEWPRICE AS VARCHAR(10)) || '
   WHERE
      ProductID = ' || CAST(:PRODUCTID AS VARCHAR(10)) || '
      AND EndDate IS NULL;';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE :DYNSQLSTATEMENT;
  END;
$$;

CREATE PROCEDURE GetVacationHours  
   @employeeId INT,  
   @vacationHours INT OUTPUT  
AS  
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS OUT INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
   END;
$$;

CREATE PROCEDURE OPTIONAL_PARAMETER @VAR1 INT = 1, @VAR2 INT = 2
AS
    BEGIN
        RETURN NULL;
    END

GO

EXEC OPTIONAL_PARAMETER @VAR2 = 4

CREATE OR REPLACE PROCEDURE OPTIONAL_PARAMETER (VAR1 INT DEFAULT 1, VAR2 INT DEFAULT 2)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        RETURN NULL;
    END;
$$;

CALL OPTIONAL_PARAMETER(VAR2 => 4);

CREATE OR ALTER PROCEDURE GetVacationHours
   @employeeId INT,  
   @vacationHours INT OUTPUT
WITH EXECUTE AS OWNER
AS
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "HumanResources.Employee" **
CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS OUT INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS OWNER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
   END;
$$;

CREATE OR ALTER PROCEDURE GetVacationHours
   @employeeId INT,  
   @vacationHours INT OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN  
   SELECT @vacationHours = VacationHours 
   FROM HumanResources.Employee
   WHERE NationalIDNumber = @employeeID
END;

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "HumanResources.Employee" **
CREATE OR REPLACE PROCEDURE GetVacationHours (EMPLOYEEID INT, VACATIONHOURS OUT INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT
         VacationHours
      INTO
         :VACATIONHOURS
      FROM
         HumanResources.Employee
      WHERE
         NationalIDNumber = :EMPLOYEEID;
   END;
$$;

 CREATE OR ALTER PROCEDURE GetVacationHours
   @Param1 INT READONLY,  
   @Param2 INT VARYING
AS
BEGIN  
   SELECT * FROM Table1;
END;

CREATE OR REPLACE PROCEDURE GetVacationHours (PARAM1 INT !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'READONLY PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!, PARAM2 INT !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'VARYING PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!)
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      ProcedureResultSet RESULTSET;
   BEGIN
      ProcedureResultSet := (
      SELECT
         *
      FROM
         Table1);
      RETURN TABLE(ProcedureResultSet);
   END;
$$;

CREATE TABLE vEmployee   (
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255),
);

INSERT INTO vEmployee(PersonID, LastName, FirstName) 
VALUES
    (1, 'AA', 'A'),
    (2, 'BB', 'B'),
    (3, 'CC', 'C'),
    (4, 'DD', 'D'),
    (5, 'EE', 'E'),
    (6, 'FF', 'F'),
    (7, 'GG', 'G');

CREATE OR ALTER PROCEDURE CursorExample
AS
    DECLARE 
        @CursorVar CURSOR, 
	@firstName VARCHAR;

    SET @CursorVar = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
	FOR  
	SELECT FirstName
	FROM vEmployee;  

    OPEN @CursorVar;

    FETCH NEXT FROM @CursorVar INTO @firstName;
    FETCH NEXT FROM @CursorVar INTO @firstName;

    CLOSE @CursorVar;

    SELECT @firstName;
GO

CREATE OR REPLACE TABLE vEmployee (
	PersonID INT,
	LastName VARCHAR(255),
	FirstName VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES
    (1, 'AA', 'A'),
    (2, 'BB', 'B'),
    (3, 'CC', 'C'),
    (4, 'DD', 'D'),
    (5, 'EE', 'E'),
    (6, 'FF', 'F'),
    (7, 'GG', 'G');

CREATE OR REPLACE PROCEDURE CursorExample ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		CURSORVAR CURSOR
		FOR
			SELECT FirstName
			FROM vEmployee;
		FIRSTNAME VARCHAR;
		ProcedureResultSet RESULTSET;
	BEGIN
		 
		 
		OPEN CURSORVAR;
		FETCH
			CURSORVAR
		INTO
			:FIRSTNAME;
		FETCH
			CURSORVAR
		INTO
			:FIRSTNAME;
		CLOSE CURSORVAR;
		ProcedureResultSet := (
		SELECT
			:FIRSTNAME);
		RETURN TABLE(ProcedureResultSet);
	END;
$$;

CREATE OR ALTER PROCEDURE cursor_procedure1
AS
BEGIN
DECLARE cursor1 CURSOR FOR SELECT col1 FROM my_table;
WHILE 1=0
   BEGIN
      FETCH NEXT FROM @cursor1 INTO @variable1;
   END
END;

CREATE OR REPLACE PROCEDURE cursor_procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      --** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
      cursor1 CURSOR
      FOR
         SELECT
            col1
         FROM
            my_table;
   BEGIN
       
      WHILE (1=0) LOOP
         --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
         FETCH
            CURSOR1
            INTO
            :VARIABLE1;
      END LOOP;
   END;
$$;

DECLARE @find VARCHAR(30);
DECLARE @find2 VARCHAR(30) = 'Default';
DECLARE @var VARCHAR(5), @var2 varchar(5);

DECLARE
    FIND VARCHAR(30);
    FIND2 VARCHAR(30) := 'Default';
    VAR VARCHAR(5);
    VAR2 VARCHAR(5);
BEGIN
    RETURN '';
END;

DECLARE @MyTableVar TABLE(  
    column1 varchar(10));

BEGIN
    DECLARE
        T_MYTABLEVAR TABLE(
            column1 VARCHAR(10));
END;

DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;

DECLARE
    _GROUP VARCHAR(50);
    SALES NUMBER(38, 4);
BEGIN
    _GROUP := 'North America';
    SALES := 2000000;
END;

DECLARE @Group nvarchar(50), @Sales MONEY;

DECLARE
    _GROUP VARCHAR(50);
    SALES NUMBER(38, 4);
BEGIN
    RETURN '';
END;

CREATE OR ALTER PROCEDURE AddPrivacyDepartment
AS 
EXECUTE ('INSERT INTO HumanResources.Department VALUES (''Privacy'', ''Executive General and Administration'', default)');

CREATE OR REPLACE PROCEDURE AddPrivacyDepartment ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO HumanResources.Department VALUES ('Privacy', 'Executive General and Administration', default);';
END;
$$;

EXECUTE AddPrivacyDepartment;
SELECT DepartmentID, Name, GroupName FROM HumanResources.Department; 

 CALL AddPrivacyDepartment();

SELECT
DepartmentID,
Name,
GroupName
FROM
HumanResources.Department;