Stored Functions in T/SQL
A Function is a block of code that will perform some operations on the given values and must return a value.
Stored functions are also called a User-defined Function:
The user can define two types of Stored Functions:
— Scalar Valued Functions
— Table Valued Functions
Working with Scalar valued Function:
In this, a function will return a single value or a single column value from the table.
SYNTAX:
CREATE [OR ALTER] FUNCTION <FUNCTION NAME> (@<PARAMETER NAME1><DATA TYPE >[SIZE], ………………..)
RETURNS <return PARAMETER / ATTRIBUTE/ VARIABLE DATAYPE>
AS
BEGIN
<FUNCTION BODY / STATEMENTS>
RETURN <return PARAMETER / ATTRIBUTE/ VARIABLE NAME>
END
SYNTAX TO CALL A SCALAR VALUED FUNCTION:
SELECT dbo.<FNAME> (VALUE / VALUES)
Working with Table valued Function:
In this, a function will return more than one value / more than one column value from the table.
CREATE [OR ALTER] FUNCTION <FUNCTION NAME> (@<PARAMETER NAME1><DATA TYPE >[SIZE], ………………..)
RETURNS TABLE
AS
RETURNS TABLE
SYNTAX TO CALL A TABLE VALUED FUNCTION:
SELECT * FROM <FUNCTION NAME> (VALUE / VALUES);
Let’s consider a table named as PLANTS
Code:
CREATE FUNCTION TVF1(@CATEGORY VARCHAR (40))
RETURNS TABLE
AS
RETURN (SELECT * FROM PLANTS WHERE CATEGORY=@CATEGORY)
SELECT * FROM TVF1(‘OFFICE’)
Output:
