SQL Function

 1-Scalar Function

  --Create function to get emp full name 

Create function fnGetEmpFullName

(

 @FirstName varchar(50),

 @LastName varchar(50)

)

returns varchar(101)

As

Begin return (Select @FirstName + ' '+ @LastName);

end 

Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 

---------------------------------------------------------------------------

2- Table-Valued Function

Create function fnGetEmployee()

returns Table

As

 return (Select * from Employee)

----------------------------------------------

3- Multi-Statement Table-Valued Function

Create function fnGetMulEmployee()

returns @Emp Table

(

EmpID int, 

FirstName varchar(50),

Salary int

)

As

begin

 Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;

--Now update salary of first employee

 update @Emp set Salary=25000 where EmpID=1;

--It will update only in @Emp table not in Original Employee table

return

end 

Select * from fnGetMulEmployee()