Scribd
Upload
11 views

Stored Functions

Uploaded by

sriprasad.a14
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views

Stored Functions

Uploaded by

sriprasad.a14
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Stored Functions: Function is a block of code similar to a stored procedure which is

also used to perform an action and returns result as a value. Function can be divided
into two types, these are

1)Scalar-Valued Fuction: In this case we can return a attribute datatype as an output


from the function and return a single value.

Syntax: Create Function <Function Name> (@parameter <Data Type> [size])

Returns <return attribute data type>

As

Begin

<Function Body>

Return <return attribute name>

End

How to Call Scalar valued Functions:

Syntax: Select dbo.<Function Name> (value)

1) Create a function to return the cube of the given value.


create function fcube (@x int)
returns int
as
begin
return @x*@x*@x
end

Output:select dbo.fcube(3)
2) Create a function that takes an employee id and returns the salary of that
employee.
create function fsal(@eid int)
returns money
as
begin
declare @sal money
select @sal=salary from employee where empid=@eid
return @sal
end
Output:select dbo.fsal(1)
3) Create function to accept department name and return sum of salary of given
department?
CREATE FUNCTION SF3(@DNAME VARCHAR(10))
RETURNS MONEY
AS
BEGIN
DECLARE @TOTSAL MONEY
SELECT @TOTSAL=SUM(SALARY) FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO AND DNAME=@DNAME
RETURN @TOTSAL
END

SELECT dbo.SF3('SALES')
4) Create function to accept dates and returns no.of employee ?
CREATE FUNCTION SF4(@SD DATE,@ED DATE)
RETURNS INT
AS
BEGIN
DECLARE @NUMEMP INT
SELECT @NUMEMP=COUNT(*) FROM EMP WHERE
HIREDATE BETWEEN @SD AND @ED
RETURN @NUMEMP
END

SELECT dbo.SF4('1981-01-01','1981-12-31')

5) Create function to accept employee number and return that employee


Gross salary based on following conditions are
1. Hra – 10% ,2.Da – 20%,3.Pf – 10% on basic salary ?

CREATE FUNCTION SF5(@EMPNO INT)


RETURNS MONEY
AS
BEGIN
DECLARE @BSAL MONEY,@HRA MONEY,@DA MONEY,@PF
MONEY,@GROSS MONEY
SELECT @BSAL=SALARY FROM EMP WHERE EMPNO=@EMPNO
SET @HRA=@BSAL*0.1
SET @DA=@BSAL*0.2
SET @PF=@BSAL*0.1
SET @GROSS=@BSAL+@HRA+@DA+@PF
RETURN @GROSS
END

Output :
Select dbo.sf5(7788)

2)Table-Valued Fuction:In this case we can return a table as an output from the
function.

Syntax:

Create Function <Function Name> (@parameter <Data Type> [size])

Returns <Table>

As

Return <return select statement>

How to Call a Table-Valued Function:

Syntax: select * from functionname(value)

Ex: Create a function that accept the Address and returns the list of employee working
in given address from the table.

create function ft1(@add varchar(50))


returns table
as
return(select * from employee where address=@add)

Output:select * from ft1('hyd')

Ex:Create a function to get the deptno and return list of employee working in EMP and
DEPT tables.

create function saidata(@deptno int)


returns table
as
return(select e.eid,e.ename,e.salary,d.deptno,d.dname,d.location from emp e inner join
dept d on e.deptno=d.deptno where e.deptno=@deptno)

Output: Select * from saidata(10)


Ex:Retrieving data from more than two tables by using function?

CREATE FUNCTION TVF4(@DNAME VARCHAR(10))

RETURNS TABLE

AS

RETURN(SELECT E.EMPNO,E.ENAME,E.SALARY,S.LOSAL,S.HISAL,

D.DNAME,D.LOC,D.DEPTNO FROM EMP E INNER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO INNER JOIN SGRADE S ON E.SALARY BETWEEN

S.LOSAL AND S.HISAL WHERE DNAME=@DNAME)

How To Drop Functions:

 Drop Function <Function Name>

Ex: Drop Function Saidata

Difference between Function And Procedure:

 A function must return a value where as procedure may or may not be returns a
value.
 When a procedure want to return a value then we should use “OUT” parameter
whereas a function want to return a value then we use “RETURN” statement.
 A procedure can have parameters of both input (with parameters) and output
(without parameters) where as a function can have only input (with parameters)
parameters only.
 In procedure we can perform select, insert, update and delete operation where
as function can used only to perform select. Cannot be used to perform insert,
update and delete operations.
 A procedure provides the option for to perform transaction management where
as these operations are not permitted in a function.
 Procedures supporting to use exception handling mechanism but functions are
not supporting exception handling mechanism.
 We call a procedure using execute command where as function are called by
using select command only

You might also like