user defined functions
are the stored sub programs that must return a value with return statement
scalar functions
table valued functions
scalar functions
are the user defined functions that returns a single value. to create a function use the create function command
that has the following syntax
create function <functionname> [with encrypton] as
begin
<Statement>
return <value>
end
create function sum(@a int,@b int,@c int,@d int,@e int) returns int
as
begin
return @a+@b+@c+@d+@e
end
To execute a function use the "Select statement" while calling a user defined scalar function, you must write the
function name as "schemaname.functionname" and not directly function name
select dbo.sum(10,20,30,40,50)
by default schemaname will be same as current username
create a function to automatically generate new department no that can be used while inserting rows into dept table
insert DEPT values(dbo.newdno(),'testing','hyd')
Table valued functions
these are the functions that returns a table
table valued functions are classified into
inline tabled valued function
multi statement table valued function
inline table valued function
these are the table valued function that contains a single statement that is the return statement
that contains a select statement withinit, whose result is converted as a inline as a table and returned.
to create a inline table valued function create function command has the following syntax
inline table valued function must not have the keyword begin and end
create a function that accept deptno as argument and returns a list of employees wrong in that department along with deptname
and location
getting data
multi statement table valued function
these are the table valued function , that contains multiple function, in this you have to create a table manual, insert
rows into it and return it
to create a multi statemen table valued function syntax
create a function to return employee no, employeename, salary, commission for all employees along with total and annual salary
scalar function
inline function
multi statement table valued function
getting defination of a function
to get the defination of a function , use the stored procedure sp_helptext with the function name as argument
for not displaying defination of our function , use "encryption option" while creating the function
permissions
when the function is a scalar function , than you have to grant execute permission and when the funcion is table valued function than you can grant select, insert, update, and delete
deleting a function
to delete a function , use drop function command
the following example deletes the function sum
select dbo.sum(10,20,30,40,50)
by default schemaname will be same as current username
create a function to automatically generate new department no that can be used while inserting rows into dept table
Create function newdno() returns int
as
begin
declare @ndno int
select @ndno=isnull(max(deptno),0)+10 from dept
return @ndno
end
select dbo.newdno()
Create function get_Tsal(@empno int)
returns money
as
begin
declare @tsal money
select @tsal=sal+isnull(comm,0) from emp where empno=@empno
return @tsal
end
select dbo.get_Tsal(1014)
Table valued functions
these are the functions that returns a table
table valued functions are classified into
inline tabled valued function
multi statement table valued function
inline table valued function
these are the table valued function that contains a single statement that is the return statement
that contains a select statement withinit, whose result is converted as a inline as a table and returned.
to create a inline table valued function create function command has the following syntax
create function <functionname> ([parameter])
returns table [with encrption ] as
return (<selectstatement>)
inline table valued function must not have the keyword begin and end
create a function that accept deptno as argument and returns a list of employees wrong in that department along with deptname
and location
create function getemp(@dno int) returns table
as
return (selecte.*,dname,loc from emp e inner join dept d on e.deptno=d.deptno and e.deptno=@dno)
as table valued function, returns a table, you can perform select, insert, update, delete on a table valued function, but in
case of a view any operation you perform on a table valued function will be performed on the table from which function is getting data
While calling a table valued funcion , no need to write the function name in the form of schemaname.functoinname and you can write the function name directly
select * from getemp(20)
multi statement table valued function
these are the table valued function , that contains multiple function, in this you have to create a table manual, insert
rows into it and return it
to create a multi statemen table valued function syntax
create function <functionname> ([parameter])
returns @<tablename> table (<colname> datatype ,......)
[with encryption] as begin
<Stmts>
return
end
create a function to return employee no, employeename, salary, commission for all employees along with total and annual salary
create function getsal() returns @empsal table(
empno int,ename varchar(20),salary money, commission money, totalsalary money,annualsalry money)
as
begin
declare empcur cursor for select empno,ename, sal ,comm from emp
declare @eno int,@en varchar(30), @s money
declare @c money, @ts money, @as money
open empcur
fetch next from empcur into @eno,@en,@s,@c
while @@FETCH_STATUS=0
begin
set @ts=@s+ISNULL(@c,0)
set @as=@ts*12
insert @empsal values (@eno,@en,@s,@c,@ts,@as)
fetch next from empcur into @eno,@en,@s,@c
end
deallocate empcur
return
end
select *from getsal()
the above function can also be created as inline function valued function as follows
create function getsal1() returns table as
return (Selectempno,ename,sal as salary, comm as commission ,sal+isnull(comm,0) as totolsalary, (sal+isnull(comm,0))*12 as annulslary fromemp)
to get list of user defined functions
to get the list of user defined functions use the following select statementscalar function
select * from sys.objects where type='fn'
inline function
select * from sys.objects where type='if'
select * from sys.objects where type='tf'
all functions select * from sys.objects where type in ('tf','if','fn')
getting defination of a function
to get the defination of a function , use the stored procedure sp_helptext with the function name as argument
for not displaying defination of our function , use "encryption option" while creating the function
permissions
when the function is a scalar function , than you have to grant execute permission and when the funcion is table valued function than you can grant select, insert, update, and delete
deleting a function
to delete a function , use drop function command
the following example deletes the function sum
drop function sum
SCALAR FUNCTIONS
create function get_sal(@empno int)
returns money
as
begin
declare @tsal money
select @tsal=SAL+ISNULL(comm,0) from EMP where EMPNO=@empno
return @tsal
end
select dbo.get_Sal(1014)
create function addnum(@x int, @y int)
returns int
as
begin
declare @z int
set @z=@x+@y
return @z
end
select dbo.addnum(10,14)
Executing the function from another TSQL Block
declare @a int, @b int, @c int
set @a=30
set @b=50
set @c=dbo.addnum(@a,@b)
print @c
Create a functoin to calculate employee exp
create function expr(@e int)
returns int
as
begin
declare @doj datetime ,@x int
select @doj=hiredate from emp where empno=@e
set @x=abs(datediff(yy,getdate(), @doj))
return @x
end
select dbo.expr(7566)
Create a function to return the cube of given value
create function fun1(@n int)
returns int
as
begin
return @n+@n+@n
end
select dbo.fun1(7)
Create a function to return the area of triangle
create function fun2(@n int,@m int)
returns int
as
begin
return @n*@m
end
select dbo.fun2(4,5)
Create a function to accept employee no, return the salary of the emp from emp table
create function fun3(@teno int)
returns int
as
begin
declare @tsal int
select @tsal=sal from emp where empno=@teno
return @tsal
end
select dbo.fun3(7369)
InLINE FUNCTION
create function get_ed_data(@deptno int)
returns table
as
return(select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno where
e.deptno=@deptno)
multi statement table valued function
Calling the above function
select * from get_ed_data(10)
select deptno,dname fromget_ed_data(10)
multi statement table valued function
create function get_Empdata(@empno int)
returns @mytable table(totalsalary money, annsal money)
as
begin
declare @sal money, @comm money
declare @tsal money, @asal money
select @sal=sal , @comm=comm from emp where empno=@empno
set @tsal=@sal+isnull(@comm,0)
set @asal=(@sal+isnull(@comm,0))*12
insert into @mytable values(@tsal , @asal)
return
end
select * from get_Empdata(1005)
No comments:
Post a Comment
Thank you for visiting my blog