//* ----USER DEFINED
FUNCTION---- *//
//*
There
are two types of User Defined Function
1-Scalar UDFs
2-Table-valued UDFs
------- Scalar UDFs -----------
Scalar functions may or may not have parameters,
but always return a single (scalar) value.
To
create a function, we use the following syntax:
*//
CREATE FUNCTION Function_Name(@Parameter1
DataType,
@Parameter2 DataType,......@Parametern
Datatype)
RETURNS Return_Datatype
AS
BEGIN
Function Body
Return Return_Datatype
END
//* Example #01
Write
a Function to get the date in the formate of Month day year *//
CREATE FUNCTION GetServerDate()
RETURNS varchar(20)
AS
BEGIN
RETURN Convert(Varchar(20), SysDateTime(), 107)
End
--Command(s) completed successfully.
SELECT dbo.GetServerDate()
--May 19, 2016
//* Example 02
write
a function to claculate the age. *//
CREATE FUNCTION calculateAge(@DOB Date)
RETURNS INT
AS
BEGIN
DECLARE @Age INT
SET @Age = DATEDIFF(YEAR, @DOB, GETDATE()) -
CASE
WHEN (MONTH(@DOB) > MONTH(GETDATE()))
OR
(MONTH(@DOB)
= MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE()))
THEN 1 ELSE 0 END
RETURN @Age
END
Select dbo.calculateAge('10/08/1981')
//*
(No
column name)
34 *//
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
//* Example#03
write a
function to claculate the TotalJob Experince. AdventureWorks2008 *//
select NationalIDNumber,JobTitle,MaritalStatus,Gender,BirthDate,
dbo.calculateAge(BirthDate) as age
from HumanResources.Employee
CREATE FUNCTION calculateTotalJOb(@HD Date)
RETURNS INT
AS
BEGIN
DECLARE @TotalYear INT
SET @TotalYear = DATEDIFF(YEAR, @HD, GETDATE()) -
CASE
WHEN (MONTH(@HD) > MONTH(GETDATE()))
OR
(MONTH(@HD)
= MONTH(GETDATE()) AND DAY(@HD) > DAY(GETDATE()))
THEN 1 ELSE 0 END
RETURN @TotalYear
END
select NationalIDNumber,JobTitle,MaritalStatus,Gender,
HireDate,
dbo.calculateTotalJOb(HireDate) as TotalJob
from HumanResources.Employee
//*
NationalIDNumber JobTitle MaritalStatus Gender HireDate TotalJob
295847284 Chief
Executive Officer S M 1999-02-15 17
245797967 Vice
President of Engineering S F 1998-03-03 18
509647174 Engineering
Manager M M 1997-12-12 18
112457891 Senior
Tool Designer S M 1998-01-05 18
*//
select NationalIDNumber,JobTitle,MaritalStatus,Gender,
HireDate,
dbo.calculateTotalJOb(HireDate) as TotalJob
from HumanResources.Employee
where dbo.calculateTotalJOb(HireDate)>18
//*
NationalIDNumber JobTitle MaritalStatus Gender HireDate TotalJob
253022876 Marketing
Assistant S M 1997-02-26 19
14417807 Production
Technician - WC60 M M 1996-07-31 19
*//
---write a function to get the email address
---------
CREATE FUNCTION PersonEmail(@BusinessEntityID INT)
RETURNS VARCHAR(250)
AS
BEGIN
RETURN (
SELECT EmailAddress
FROM Person.EmailAddress
WHERE BusinessEntityID=
@BusinessEntityID
)
END
select dbo.PersonEmail(1) as emailaddress
//*
emailaddress
ken0@adventure-works.com
*//
---
===================================================================
----=====================================================================
