SQL,User defined functions,Difference between UDF and SP,Syntax,Examples
User defined functions: :
User defined functions are divided into 2 types
1) scalar defined functions.
2) table valued user defined functions.
-> An UDF in SQL server is just similar to SP, both are stored or compiled query s.
-> An UDF will be having only input parameters.
-> UDF output can be directly used as a result set but SP's operation cannot be treated as result set.
-> In UDF we cannot use only statements which will modify current table/database state.
Note :For any query if the execution time is catched then such kind of query is called as complied query.
Difference between UDF and SP: :
|Stored Procedure||User Defined Function
|SP allows input as well as output parameters||It will allow only input parameters|
|We can return multiple result sets from SP||It can able to return max one result set.|
|We can call a SP from another SP||We cannot call a SP from UDF|
|Any type of T-SQL statements||We cannot write T-SQL data which modifies existing state of DB|
Scalar UDF :
Note :A scalar UDF will always return single scalar value.
create function fnGetsname
declare @nam varchar(40);
select @name=name from students where sid=@sid;
select dbo_fnGetsname(2)--->calling UDF
Query: Write an UDF for displaying full name of given employee (employee id as input parameter)
create function fnGetfullname
declare @fullname varchar(80);
select @fullname=fname+lname from employees where eid=@eid
select dbo.fnGetfullname(4) as 'fullname'
Query:Write a UDF for returning the employees count whose fnames are starting with given set of characters.
create function fnCountmatched Records
declare @count int;
select @count = count(*) from employees where fname like @na+'s';
Table Valued UDF :
1)Inline table valued UDF
2)Multi line table valued UDF
Query: Write an UDF for returning complete employees data.
create function GetAllEmployees()
select * from employees
Select * from dbo.GetAllEmployees()
select fname,lname,salary from dbo.GetAllEmployees()