User Defined Functions :-Types of udf’s
1) scalar udf 2) Inline udf 3) Multi Statement table udf Scalar udf : A user defined function, which returns single scalar value is called scalar udf Create a function which returns EmpName of given employee number create function getEmployeeName ( @EmpId int ) Returns varchar(15) As Begin Declare @s varchar(15) select @s=[EmpName] from [dbo].[Employee] e Where e.EmpId = @EmpId Return (@s) end Step 2: method 1 Select dbo.getEmployeeName(3) Inline udf : A user defined function which returns set of rows is called inline udf. Inline udf can have only one statement that to be return statement Ex : Create a function which returns empno, ename and salaries of given deptno employees Step 1 : --INLINE USER DEFINED FUNCTION create function getEmployees ( @Deptid int ) Returns table As Return(select e.EmpId,e.EmpName,e.EmpSalary,d.Did,d.Dname from [dbo].[Employee] e JOIN dept d on e.did=d.Did Where e.did = @Deptid) Step 2 : Select * from dbo.getEmployees(20) This function returning a table with set of rows Multistatment table udf : A udf which return set of rows with multiple statements can be called as multi statement Table udf. Return type of multistatement table udf is “table variable” Table variables : Table variables are used to store list of values (one-dimension array) or set of rows (2 dimension array temporarly) Table variables memory allocated in Prepare one dimension array to store collection of strings Declare @T1 Table (col1 varchar(20) ) Insert @T1 values(‘hello’) Insert @T1 values(‘hai’) Insert @T1 values(‘hello’) Insert @T1 values(‘hai’) Insert @T1 values(‘xyz’) Select * from @T1 Life time of table variable is as long as script executed Table variables can’t maintain the relation between other table variables Script is executed , variable name is automatically destroyed Prepare two dimensional array to store set of rows Syntax : declare @T1 Table ( Col1 int primary key, Col2 char(10) ) Insert @T1 values (10,’hello’) Insert @T1 values(20,’Hai’) Properties of table variables : 1)table variables existed in the memory as long as script executed 2) table variables can not maintain relation ship (no foreign key) Example for multistatment table udf : Create a function which returns empno, empname and salaries of given dept no employees Step 1 : create function getEmpDetailsByDno (@Deptid int) Returns @t1 table ( Empid int, Ename varchar(20), EmpSalary float, Did int, Dname varchar(20) ) As Begin Insert into @t1 (Empid,Ename,EmpSalary,Did,Dname) ---destination table select e.EmpId,e.EmpName,e.EmpSalary,d.Did,d.Dname from [dbo].[Employee] e JOIN dept d on e.did=d.Did Where e.did = @Deptid Return End Step 2: Select * from dbo.getEmpDetailsByDno(20) |
|