Scalar functions:
To create a function, we use the following syntax:
CREATE FUNCTION Function_Name(@Parameter1 DataType, @Parameter2
DataType,..@ParameternDatatype)
RETURNS Return_Datatype
AS
BEGIN
Function Body
Return Return_Datatype
END
Let us To alter a function we use ALTER FUNCTION FuncationName statement and to delete it, we use DROP FUNCTION FuncationName now create a function which calculates
and returns the age of a person. To compute the age we require, date of birth.
So, let's pass date of birth as a parameter. So, AGE() function returns an
integer and accepts date parameter.
CREATE FUNCTION Age(@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()) ANDDAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
RETURN @Age
END
When calling a scalar user-defined function, you must supply a two-part
name,OwnerName.FunctionName. dbo stands for database owner.
Select dbo.Age(dbo.Age('10/08/1982')
You can also invoke it using the complete 3 part name,
DatabaseName.OwnerName.FunctionName.
Select SampleDB.dbo.Age('10/08/1982')
Consider the Employees table below.
Scalar user defined functions can be used in the Select clause as shown below.
Select Name, DateOfBirth, dbo.Age(DateOfBirth) as Age from tblEmployees
Select Name, DateOfBirth, dbo.Age(DateOfBirth) as Age
from tblEmployees
Where dbo.Age(DateOfBirth) > 30
A stored procedure also can
accept DateOfBirth and return Age, but you cannot use stored procedures in a select or where clause. This is just one difference
between a function and a stored procedure. There are several other differences,
which we will talk about in a later session.
To view the text of the function use sp_helptext FunctionName
Nagesh Nallamari
To create a function, we use the following syntax:
CREATE FUNCTION Function_Name(@Parameter1 DataType, @Parameter2 DataType,..@ParameternDatatype)
RETURNS Return_Datatype
AS
BEGIN
Function Body
Return Return_Datatype
END
Let us To alter a function we use ALTER FUNCTION FuncationName statement and to delete it, we use DROP FUNCTION FuncationName now create a function which calculates and returns the age of a person. To compute the age we require, date of birth. So, let's pass date of birth as a parameter. So, AGE() function returns an integer and accepts date parameter.
CREATE FUNCTION Age(@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()) ANDDAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
RETURN @Age
END
When calling a scalar user-defined function, you must supply a two-part name,OwnerName.FunctionName. dbo stands for database owner.
Select dbo.Age(dbo.Age('10/08/1982')
You can also invoke it using the complete 3 part name, DatabaseName.OwnerName.FunctionName.
Select SampleDB.dbo.Age('10/08/1982')
Consider the Employees table below.
Scalar user defined functions can be used in the Select clause as shown below.
Select Name, DateOfBirth, dbo.Age(DateOfBirth) as Age from tblEmployees
Select Name, DateOfBirth, dbo.Age(DateOfBirth) as Age
from tblEmployees
Where dbo.Age(DateOfBirth) > 30
A stored procedure also can accept DateOfBirth and return Age, but you cannot use stored procedures in a select or where clause. This is just one difference between a function and a stored procedure. There are several other differences, which we will talk about in a later session.
To view the text of the function use sp_helptext FunctionName
Nagesh Nallamari
