question

Gehima2016 avatar image
Gehima2016 asked

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 , ) 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 , ) 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 , ) 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> ) RETURNS AS BEGIN -- Declare the return variable here DECLARE <@ResultVar, sysname, @Result> -- 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: -- ============================================= CREATE FUNCTION ( -- Add the parameters for the function here <@param1, sysname, @p1> , <@param2, sysname, @p2> ) RETURNS <@Table_Variable_Name, sysname, @Table_Var> TABLE ( -- Add the column definitions for the TABLE variable here , ) AS BEGIN -- Fill the table variable with the rows for your result set RETURN END GO */
homeworksyntaxudf
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What syntax errors are you seeing, and where?
2 Likes 2 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.