SnowConvert AI - SQL Server - CREATE FUNCTION¶

CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
);

GO

SELECT * from GetDepartmentInfo()

CREATE OR REPLACE FUNCTION GetDepartmentInfo ()
RETURNS TABLE(
  DepartmentID STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN DepartmentID WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  Name STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Name WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  GroupName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN GroupName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
    SELECT
    CAST(DepartmentID AS STRING),
    CAST(Name AS STRING),
    CAST(GroupName AS STRING)
    FROM
    HumanResources.Department
$$;

SELECT
    *
from
    TABLE(GetDepartmentInfo());

CREATE FUNCTION GetPersonBasicInfo()
RETURNS TABLE
AS 
RETURN
( 
 SELECT TOP (20)
      P.PersonType,
      P.FirstName,
      E.JobTitle,
   E.Gender,
      YEAR(E.HireDate) as HIREYEAR
  FROM 
      Person.Person P
  INNER JOIN 
      HumanResources.Employee E
  ON
      P.BusinessEntityID = E.BusinessEntityID 
);

GO

SELECT * FROM GetPersonBasicInfo();

CREATE OR REPLACE FUNCTION GetPersonBasicInfo ()
RETURNS TABLE(
 PersonType STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN PersonType WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
 FirstName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN FirstName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
 JobTitle STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN JobTitle WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
 Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
 HIREYEAR INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
  SELECT
  TOP 20
  CAST(P.PersonType AS STRING),
  CAST(P.FirstName AS STRING),
  CAST(E.JobTitle AS STRING),
  CAST(E.Gender AS STRING),
  YEAR(E.HireDate :: TIMESTAMP) as HIREYEAR
   FROM
  Person.Person P
   INNER JOIN
   HumanResources.Employee E
   ON P.BusinessEntityID = E.BusinessEntityID
$$;

SELECT
  *
FROM
  TABLE(GetPersonBasicInfo());

CREATE FUNCTION GetMaritalStatusByGender
(
 @P_Gender nchar(1)
)

RETURNS TABLE 
AS
RETURN
(
  WITH CTE AS 
 (
  SELECT BusinessEntityID, MaritalStatus, Gender 
  FROM HumanResources.Employee 
  where Gender = @P_Gender
 ) 
  SELECT 
 MaritalStatus, Gender, CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM 
 CTE INNER JOIN Person.Person P
  ON
 CTE.BusinessEntityID = P.BusinessEntityID
);

GO

select * from GetMaritalStatusByGender('F');

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "HumanResources.Employee", "Person.Person" **
CREATE OR REPLACE FUNCTION GetMaritalStatusByGender
(P_GENDER STRING
)
RETURNS TABLE(
 MaritalStatus STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN MaritalStatus WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
 Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
 Name VARCHAR
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS
$$
 --** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
  WITH CTE AS
 (
  SELECT
   BusinessEntityID,
   MaritalStatus,
   Gender
  FROM
   HumanResources.Employee
  where
   Gender = :P_GENDER
 )
  SELECT
  CAST(MaritalStatus AS STRING),
  CAST(Gender AS STRING),
  CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM
  CTE
  INNER JOIN
   Person.Person P
  ON CTE.BusinessEntityID = P.BusinessEntityID
$$;

select
  *
from
  TABLE(GetMaritalStatusByGender('F'));

CREATE OR ALTER FUNCTION calc_behavioral_segment()
RETURNS @behavioral_segments TABLE (behavioral_segment VARCHAR(50))
AS
BEGIN
 DECLARE @col varchar(15)
 SET @col = 'Unknown'
 INSERT INTO @behavioral_segments 
 SELECT @col
 
 RETURN 
END

SELECT * FROM calc_behavioral_segment();

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION calc_behavioral_segment ()
RETURNS BEHAVIORAL_SEGMENTS TABLE (
 behavioral_segment VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
 DECLARE @col varchar(15)
 SET @col = 'Unknown'
 INSERT INTO @behavioral_segments
 SELECT @col

 RETURN
END

SELECT * FROM calc_behavioral_segment();;

CREATE OR ALTER FUNCTION odd_or_even_number(@number INT)
RETURNS @numbers TABLE (number_type VARCHAR(15))
AS
BEGIN 
 IF ((@number % 2) = 0)
 BEGIN
  INSERT @numbers SELECT 'Even'
 END
    
 ELSE
 BEGIN
  INSERT @numbers SELECT 'Odd'
 END

 RETURN
END

SELECT * FROM odd_or_even_number(9);

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION odd_or_even_number (NUMBER INT)
RETURNS NUMBERS TABLE (
 number_type VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
 IF ((@number % 2) = 0)
 BEGIN
  INSERT @numbers SELECT 'Even'
 END

 ELSE
 BEGIN
  INSERT @numbers SELECT 'Odd'
 END

 RETURN
END

SELECT * FROM odd_or_even_number(9);;

CREATE OR ALTER FUNCTION new_employee_hired(@id VARCHAR (50), @position VARCHAR(50), @experience VARCHAR(15))
RETURNS @new_employee TABLE (id_employee VARCHAR (50), working_from_home BIT, team VARCHAR(15), computer VARCHAR(15)) 
AS
BEGIN 
 DECLARE @wfh BIT
 DECLARE @team VARCHAR(15)
 DECLARE @computer VARCHAR(15)

 IF @position = 'DEVELOPER'
 BEGIN
  SET @team = 'TEAM_1'
  SET @computer = 'LAPTOP'  
 END

 IF @position = 'IT'
 BEGIN  
  SET @team = 'TEAM_2'
  SET @computer = 'DESKTOP'
 END

 IF @experience = 'JUNIOR'
 BEGIN
  SET @wfh = '0'   
 END
 IF @experience = 'SENIOR'
 BEGIN
  SET @wfh = '1'      
 END

 INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
 RETURN
END

SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');

 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION new_employee_hired (ID STRING, POSITION STRING, EXPERIENCE STRING)
RETURNS NEW_EMPLOYEE TABLE (
 id_employee VARCHAR(50),
 working_from_home BOOLEAN,
 team VARCHAR(15),
 computer VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
 DECLARE @wfh BIT
 DECLARE @team VARCHAR(15)
 DECLARE @computer VARCHAR(15)

 IF @position = 'DEVELOPER'
 BEGIN
  SET @team = 'TEAM_1'
  SET @computer = 'LAPTOP'
 END

 IF @position = 'IT'
 BEGIN
  SET @team = 'TEAM_2'
  SET @computer = 'DESKTOP'
 END

 IF @experience = 'JUNIOR'
 BEGIN
  SET @wfh = '0'
 END
 IF @experience = 'SENIOR'
 BEGIN
  SET @wfh = '1'
 END

 INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
 RETURN
END

SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');;

CREATE OR ALTER FUNCTION get_employees_history()
RETURNS @employee_history TABLE (
 department_name NVARCHAR(50),
 first_name NVARCHAR(50),
 last_name NVARCHAR(50), 
 start_date DATE,
 end_date DATE,
 job_title NVARCHAR(50), 
 months_working INT
)
BEGIN
 INSERT INTO @employee_history
 SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
 LEFT OUTER JOIN employee_department_history EH
  ON D.department_ID = EH.department_ID
 INNER JOIN  Employee E
  ON E.business_entity_ID = EH.business_entity_ID
 INNER JOIN Person P
  ON P.business_entity_ID = E.business_entity_ID 
  

 UPDATE @employee_history
 SET 
  months_working = 
  CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
  ELSE DATEDIFF(MONTH, start_date, end_date)
 END
 RETURN;
END;

SELECT TOP(10) * FROM get_employees_history();

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_employees_history ()
RETURNS EMPLOYEE_HISTORY TABLE (
 department_name VARCHAR(50),
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 start_date DATE,
 end_date DATE,
 job_title VARCHAR(50),
 months_working INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
BEGIN
 INSERT INTO @employee_history
 SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
 LEFT OUTER JOIN employee_department_history EH
  ON D.department_ID = EH.department_ID
 INNER JOIN  Employee E
  ON E.business_entity_ID = EH.business_entity_ID
 INNER JOIN Person P
  ON P.business_entity_ID = E.business_entity_ID


 UPDATE @employee_history
 SET
  months_working =
  CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
  ELSE DATEDIFF(MONTH, start_date, end_date)
 END
 RETURN;
END;

SELECT TOP(10) * FROM get_employees_history();;

CREATE OR ALTER FUNCTIONcreate_new_team(@team_name VARCHAR(50))
</strong>RETURNS @new_team TABLE (type VARCHAR(50), name VARCHAR(50))
AS
BEGIN
 DECLARE @employees INT
 SET @employees = (SELECT count(*) FROM employee)
 DECLARE @type VARCHAR(15)
 SET @type = 'small_team'
 IF (@employees &#x3C; 8)
 BEGIN
  INSERT @new_team VALUES (@type, @team_name)
  RETURN
 END

 SET @type = 'big_team'
 INSERT @new_team VALUES (@type, @team_name)

 RETURN
END

SELECT * FROMcreate_new_team('Team1');

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTIONcreate_new_team (TEAM_NAME STRING)
RETURNS NEW_TEAM TABLE (
 type VARCHAR(50),
 name VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
 DECLARE @employees INT
 SET @employees = (SELECT count(*) FROM employee)
 DECLARE @type VARCHAR(15)
 SET @type = 'small_team'
 IF (@employees < 8)
 BEGIN
  INSERT @new_team VALUES (@type, @team_name)
  RETURN
 END

 SET @type = 'big_team'
 INSERT @new_team VALUES (@type, @team_name)

 RETURN
END

SELECT * FROMcreate_new_team('Team1');;

CREATE OR ALTER FUNCTION vacation_status(@id VARCHAR (50))
RETURNS @status TABLE (vacation_status VARCHAR(30))
AS
BEGIN 
 DECLARE @hire_date DATETIME
 SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
 DECLARE @vacation_hours INT
 SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
 DECLARE @time_working INT
 SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))

 IF (@vacation_hours > 0)
 BEGIN
  IF (@time_working > 3)
  BEGIN
   IF (@vacation_hours < 120)
   BEGIN
    INSERT INTO @status VALUES ('Ok')
   END

   IF (@vacation_hours = 120)
   BEGIN
    INSERT INTO @status values ('In the limit')
   END

   IF (@vacation_hours > 120)
   BEGIN
    INSERT INTO @status VALUES ('With excess')
   END
  END
  ELSE
  BEGIN
   INSERT INTO @status values ('Hired recently')
  END
 END
 ELSE
 BEGIN
  INSERT INTO @status values ('No hours')
 END
 RETURN
END

SELECT * FROM vacation_status('adventure-worksken0')

 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION vacation_status (ID STRING)
RETURNS STATUS TABLE (
 vacation_status VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
 DECLARE @hire_date DATETIME
 SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
 DECLARE @vacation_hours INT
 SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
 DECLARE @time_working INT
 SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))

 IF (@vacation_hours > 0)
 BEGIN
  IF (@time_working > 3)
  BEGIN
   IF (@vacation_hours < 120)
   BEGIN
    INSERT INTO @status VALUES ('Ok')
   END

   IF (@vacation_hours = 120)
   BEGIN
    INSERT INTO @status values ('In the limit')
   END

   IF (@vacation_hours > 120)
   BEGIN
    INSERT INTO @status VALUES ('With excess')
   END
  END
  ELSE
  BEGIN
   INSERT INTO @status values ('Hired recently')
  END
 END
 ELSE
 BEGIN
  INSERT INTO @status values ('No hours')
 END
 RETURN
END

SELECT * FROM vacation_status('adventure-worksken0');

--Additional Params: -t JavaScript
CREATE OR ALTER FUNCTION get_group_name
(@department_id INT)
RETURNS @group_names TABLE (group_name VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'  
WHILE @name = 'Another Department'
BEGIN
 IF (@department_id < 3)
 BEGIN
  SET @name = 'engineering'
 END

 IF @department_id = 3
 BEGIN
  SET @name = 'Tool Design'
 END

 SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END

SELECT * FROM get_group_name(9);

 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_group_name
(DEPARTMENT_ID INT)
RETURNS @group_names TABLE (
 group_name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'
WHILE @name = 'Another Department'
BEGIN
 IF (@department_id < 3)
 BEGIN
  SET @name = 'engineering'
 END

 IF @department_id = 3
 BEGIN
  SET @name = 'Tool Design'
 END

 SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END

SELECT * FROM get_group_name(9);;

 --Additional Params: -t JavaScript

CREATE OR ALTER FUNCTION amount_new_specimens(@id int)
RETURNS @new_specimens TABLE (amount int)
AS
BEGIN
 DECLARE @first_specimen VARCHAR(30) ;
 set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
 DECLARE @second_specimen VARCHAR(30);

 DECLARE @specimens TABLE (name_specimen VARCHAR(30))

 DECLARE Cursor1 CURSOR 
 FOR SELECT name_specimen 
 FROM specimen 

 OPEN cursor1
 FETCH NEXT FROM cursor1
 INTO @second_specimen;
 
 WHILE @@FETCH_STATUS = 0 
 BEGIN
  IF @first_specimen <> @second_specimen
  BEGIN
   INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
  END
  FETCH NEXT FROM cursor1
  INTO @second_specimen;
 END

 CLOSE cursor1;
 DEALLOCATE cursor1;

 INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
 RETURN 
END

SELECT * FROM amount_new_specimens(1);