x

UDF syntax

Hi,

Please, can someone help me check my syntax below for UDF;

 -- =============================================
 -- Author:        
 -- Create date: 08/10/2017
 -- Description:    Used to get the UserLoginID using BusinessEntityId Parameter
 -- =============================================
 
 --1) Create a UDF that accepts EmployeeID(2012,; BusinessEntityID) and returns UserLoginID. The UserLoginID is the last part of the
 --LoginID column. Its only the partthat comes after '\'
  
 
 CREATE FUNCTION Fx_GetUserLoginID
                                   ( 
                                    @BusinessEntityID int -- Add the parameters for the function here
                                   )
 RETURNS int 
 AS
 BEGIN
     
     DECLARE @BusinessEntityID int  -- Declare the return variable here
     
             SELECT RIGHT(loginID, LEN(LoginID) - CHARINDEX('\', Login)) AS UserLoginID  --Add the T-SQL statements to compute the return value here
             FROM   HumanResources.Employee
             WHERE  BusinessEntityID = @BusinessEntityID
 
     -- Return the result of the function
     RETURN UserLoginID
 
 END
 GO
 
 
 --2) Create a UDF that accepts EmployeeID (2012: BusinessEntityID) and returns their age.
 
 -- =============================================
 -- Author:        
 -- Create date: 08/10/2017
 -- Description:    Used to get the Employee Age using BusinessEntityID(EmployeeID) parameter.
 -- =============================================
 
 
 
   SELECT DATEDIFF(YY, BirthDate, GETDATE()) AS Age
   FROM  HumanResources.Employee
   WHERE BusinessEntityID = @BusinessEntityID
 
 
 CREATE FUNCTION Fx_GetAgeByBusinessEntityID 
                                           (
                                           @BusinessEntityID int
                                           )
 RETURNS int
 AS
 BEGIN
     -- Declare the return variable here
     DECLARE @BusinessEntityID int
 
              SELECT DATEDIFF(YY, BirthDate, GETDATE()) AS Age
              FROM HumanResources.Employee
              WHERE BusinessEntityID = @BusinessEntityID
 
     -- Return the result of the function
     RETURN  Age
 
 END
 GO
 
 
 
 
 
 
 
 --3) Create a UDF that accepts the Gender and returns the average vacationHours
 
 -- =============================================
 -- Author:        
 -- Create date: 08/10/2017
 -- Description:    Used to get Average Employees VacationHours using the Gender parameter
 -- =============================================
 
 
 CREATE FUNCTION Fx_GetVacationHrUsingGender
 (
     @Gender bit
 )
 RETURNS 
  TABLE 
 (
     BusinessEntityID int,
     VacationHours int
 )
 AS
 BEGIN
     (
     SELECT AVG(VacationHours) AS AvgVacationHrs
     FROM   HumanResources.Employee
     WHERE  Gender = @Gender
      )
     RETURN AvgVacationHrs
 END
 GO
 
 
 
 --4)Create a UDF that accepts ManagerID(2012: Jobtitle) and returns all of that ManagerID(2012: JobTitle) Employee Information.
 --a) LoginID
 
 
 
 -- =============================================
 -- Author:        Gabriel Ehima
 -- Create date: 
 -- Description:    Used to get Manager's Employee's Information by using LoginID parameter
 -- =============================================
 CREATE FUNCTION GetMgrEmployeeInfo
 (
                 @LoginID int
 )
 RETURNS 
  TABLE 
 (
     -- Add the column definitions for the TABLE variable here
     <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
     <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
 )
 AS
     BEGIN
             SELECT * AS MgrEmployeeInfo                       
             FROM   HumanResources.Employee
             WHERE  Title Like '%Manager%'
             AND    LoginID = @LoginID
     RETURN  MgrEmployeeInfo
 END
 GO
 
 
 
 --4)Create a UDF that accepts ManagerID(2012: Jobtitle) and returns all of that ManagerID(2012: JobTitle) Employee Information.
 --b) Gender
 
 
  
 -- =============================================
 -- Author:        
 -- Create date: 08/10/2017
 -- Description:    Used to get Manager's Employee's Information by using Gender parameter
 -- =============================================
 CREATE FUNCTION GetMgrEmployeeInfo
 (
                 Gender bit
 )
 RETURNS 
  TABLE 
 (
     -- Add the column definitions for the TABLE variable here
     <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
     <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
 )
 AS
 BEGIN
             SELECT *                        
             FROM   HumanResources.Employee
             WHERE  Title Like '%Manager%'
             AND    Gender = @Gender
     
     RETURN MgrEmployeeInfo
 END
 GO
 
 
 
 
 --4)Create a UDF that accepts ManagerID(2012: Jobtitle) and returns all of that ManagerID(2012: JobTitle) Employee Information.
 --c) HireDate
 
 -- =============================================
 -- Author:        
 -- Create date: 08/10/2017
 -- Description:    Used to get Manager's Employee's Information by using HireDate parameter
 -- =============================================
 CREATE FUNCTION GetMgrEmployeeInfo
 (
                 HireDate datetime
 )
 RETURNS 
  TABLE 
 (
     -- Add the column definitions for the TABLE variable here
     <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
     <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
 )
 AS
 BEGIN
             SELECT *                        
             FROM   HumanResources.Employee
             WHERE  Title Like '%Manager%'
             AND    HireDate = @HireDate
     
     RETURN MgrEmployeeInfo
 END
 GO
 
 /* Scalar function
 
 CREATE FUNCTION  
 (
     -- Add the parameters for the function here
     <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
 )
 RETURNS 
 AS
 BEGIN
     -- Declare the return variable here
     DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
 
     -- Add the T-SQL statements to compute the return value here
     SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
 
     -- Return the result of the function
     RETURN <@ResultVar, sysname, @Result>
 
 END
 GO
 
 
 
 
 Table Function
 -- =============================================
 -- Author:        
 -- Create date: 08/10/2017
 -- Description:    <Description,,>
 -- =============================================
 CREATE FUNCTION  
 (
     -- Add the parameters for the function here
     <@param1, sysname, @p1> <data_type_for_param1, , int>, 
     <@param2, sysname, @p2> <data_type_for_param2, , char>
 )
 RETURNS 
 <@Table_Variable_Name, sysname, @Table_Var> TABLE 
 (
     -- Add the column definitions for the TABLE variable here
     <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
     <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
 )
 AS
 BEGIN
     -- Fill the table variable with the rows for your result set
     
     RETURN 
 END
 GO
 
 */
more ▼

asked Aug 11 at 07:28 AM in Default

avatar image

Gehima2016
1

What syntax errors are you seeing, and where?

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x103
x34
x11

asked: 6 days ago

Seen: 22 times

Last Updated: yesterday

Copyright 2017 Redgate Software. Privacy Policy