Functions in SQL Server

In SQL Server there are two types of Functions:

Scalar Value functions and Table Value Functions.

1.) Scalar Value Functions :


create function emp_name
(
@Name varchar(20)
)
RETURNS Decimal(12,4)
AS
BEGIN
DECLARE @SAL Decimal(12,4);
SELECT @SAL = (SELECT Max(Salary) from employee_log where Name like '%' + @Name + '%');
RETURN @SAL;
END;
GO

Scalar Value Functions return single value which can be used in SELECT or WHERE Clause.

Select * from employee_log where Salary = [dbo].[emp_name]('Rohan');

2.) Table Valued Functions : Table value functions return table as an output. Table Value Functions are of two types :
Inline Table Valued Function and Multi-statement Table Valued Function.

1.) Inline Table Valued Function
create function emp_log
(
@Action as  varchar(20)
)
RETURNS TABLE as RETURN
(
SELECT Empid,Name,Salary,Action,date_time
from employee_log
where Action like '%' + @Action + '%'
)
GO

select * from emp_log('DELETE');

2.) Multi-statement Table Valued Function:

create function emp_log_table

(

@Action as  varchar(20)

)

RETURNS @returntable TABLE

(

Empid  int,

Name   varchar(50),

Salary  decimal (12,4),

Action  varchar(50),

date_time  datetime

)

AS

BEGIN

INSERT @returntable

SELECT Empid,Name,Salary,Action,date_time

from employee_log

where Action like '%' + @Action + '%'

RETURN

END;

GO

select * from emp_log_table('DELETE');