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 ProcedureUser Defined Function
SP allows input as well as output parametersIt will allow only input parameters
We can return multiple result sets from SPIt can able to return max one result set.
We can call a SP from another SPWe cannot call a SP from UDF
Any type of T-SQL statementsWe cannot write T-SQL data which modifies existing state of DB

Scalar UDF :

Syntax: create function (parameters list) returns datatype[size] as begin statement 1 statement 2 return end Note :A scalar UDF will always return single scalar value. Ex: create function fnGetsname ( @sid int ) returns varchar(40) as begin declare @nam varchar(40); select @name=name from students where sid=@sid; return @name end 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 ( @eid int ) returns varchar(80) as begin declare @fullname varchar(80); select @fullname=fname+lname from employees where eid=@eid return @fullname end 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 ( @na varchar(40) ) returns int as begin declare @count int; select @count = count(*) from employees where fname like @na+'s'; return @count end

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() returns table as return ( select * from employees ) Select * from dbo.GetAllEmployees() select fname,lname,salary from dbo.GetAllEmployees()