User Defined Function

User Defined FUNCTION

Function can be defined as a Sub Programs which Performs an action this can be called from other Programs. Function should return Value.

Eg.1

CREATE FUNCTION NETSAL (@BPAY INT, @HRA INT, @DA INT)

RETURNS INT

BEGIN

RETURN (@BPAY+@HRA+@DA)

END

Calling Program

DECLARE @NS INT

EXEC @NS = NETSAL 4000, 3000, 2000

SELECT @NS ‘NET SALARY FOR THE EMPLOYEE’

O/P – NET SALARY FOR THE EMPLOYEE 9000

Eg: 2

CREATE FUNCTION CALC (@N VARCHAR (20))

RETURNS INT

BEGIN

DECLARE

@SAL INT, @COMM INT

SELECT @SAL=SAL FROM EMPLOYEES WHERE ENAME = @N

SET @COMM = SAL * .1

RETURN @COMM

END

Calling Program

DECLARE @C INT

EXEC @C AS ‘Commission For The Employee’

Dropping a Function

DROP FUNCTION NETSAL