Functions In SQL Server*

We cover user defined function in this session

User Defined Function
--------*1 Scalar-Valued Function
-----------**2 Inline Table Valued Function
-----------------***3 Multi-Line Valued Function


Function Calling Syntax
--------Select OwnerName.FunctionName()-----1st type
--------Select DatabaseName.OwnerName.FunctionName()-----2nd type

Here OwnerName is Database Owner


Create Table For Perform Function On it.
create table tblEmployee1
(
Id int,
Name nvarchar(50),
Salary int 
)

Insert Value in Table tblEmployee1

insert into tblEmployee1 values (1,'John',4000)
insert into tblEmployee1 values (2,'Sara',4500)
insert into tblEmployee1 values (3,'Clark',3000)
insert into tblEmployee1 values (4,'Hyden',5000)
insert into tblEmployee1 values (5,'Ponting',8000)


-----*1 Scalar-Valued Function------------
Scalar-Valued Function Return Single Column Value only or return Single Value

Creating Scalar function for GetFullname of employee
Create function FnScalar_GetEmployeeName
(
@firstName varchar(50)
)
returns varchar(50)
as
begin
return  (select @firstName)
end

Calling Scaler Function Function
select dbo.FnScalar_GetEmployeeName(Name) from tblEmployee1


---------*******************-------------

----**2 Inline Valued Function-------
User Define inline Valued Function Return Table Variable As a result of action

Create Inline valued Function
create function FnInline_GetEmployeeName()
RETURNS TABLE
AS
RETURN (SELECT * FROM tblEmployee1)

Calling Inline Function

SELECT * FROM FnInline_GetEmployeeName()

---------*******************-------------

---***3 MultiLine Valued Function------

Create MultiLine Valued Function

Create Function For Id, orginalName, And Salary of tblEmpoyee1
Create Function FnMultiline_GetEmoloyeeDetail()
returns @Employee Table
(
Id int,
Name varchar(50),
Salary int
)
as
Begin
    Insert into @Employee Select e.Id,e.Name,e.Salary from tblEmployee1 e;
-----Now Update Salary of Clark
Update @Employee set Salary = 5000 Where Id =1;
-----It Will Update @Employee Table Runtime only Orginal Database Table
return
End

Calling  MultiLine Valued Function

Select * from FnMultiline_GetEmoloyeeDetail()

---------*******************-------------

Different Between Store Procedure and Function
1 Function Used with Select and Where clause, but StoreProdcedure  Not
Like
        Select dbo.FnScalar_GetEmployeeName(Name) as Employee Name From tblEmployee1

2 Try-Catch blog Is not in function, but in Store Procedure have

3 Function only input Parameter but in StoreProcedure both input/output parameter

4 Function Can not call StoreProdcedure, but in StoreProdcedure we call Function

5 Function Must Return Value, but StoreProdcedure is optional