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:

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:

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

PLANT

Code:

CREATE FUNCTION TVF1(@CATEGORY VARCHAR (40))

RETURNS TABLE

AS

RETURN (SELECT * FROM PLANTS WHERE CATEGORY=@CATEGORY)

SELECT * FROM TVF1(‘OFFICE’)

Output:

Output

- Computer Science Engineer