SQL User defined Functions

 3 types of User defined Functions:

1.Scalar -Returns single value 

2.Inline Table Valued Function    -returns a table (does not have begin and end body)

3.Multi Statement Table Valued Function  

1. Scalar Function

scalar function returns single value, uses return keyword. has or has not any parameters 

example(if we have to calculate dateofbirth or intrest or profit which has single value):

ALTER Function [dbo].[f_CountTotal](@salary money)

Returns Money  --returns single value therefore defining the sing value's data type

As

Begin 

Declare

@Total money,

@TotalSal money

  set @Total= (select SUM(EmployeeId) from Employee)

   set @TotalSal= @salary * @Total

   return @TotalSal --uses return keyword.

   End

2.Inline Table Valued function

It returns a table. It uses Return table keyword. does not have any begin end block. The returned table can be used to achieve the functionality of a parameterized views. Can be used in Joins.

Didn't practiced in pum. 

example:

CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType)
RETURNS TABLE
AS
RETURN (Select_Statement)

3. Multi Statement Table Valued Function

It is somehow similar with Inline table valued function but it doesn't have parameters but has begin and end block. 

Example:

create Function fn_GetEmployee()

Returns @EmployeeTable Table(Name Varchar(max),EmployeeId Varchar(max))

As

Begin

  Insert into @EmployeeTable select Name,EmployeeId from Employee

  Return

  End

  select * from dbo.fn_GetEmployee()

NOTE: EXECUTION IS FASTER IN INLINE TABLE VALUED FUNCTION THAN IN MULTILINE TABLE VALUED FUNCTION BECOUSE SQL TREATS INLINE TABLE VALUED FUNTION AS A VIEW. 

ABSTRACTION:  Well I've observed PUM and noticed that there's static function and hybrid (multi statement and inline). the reason I have called hybrid is because stupid Venkat said that, Inline does not have begin and end and the next thing he said is Multi statement does not take parameter. 

after analyzing the style of function in PUM i came to know that, any function can take parameter, return table with table structure defined in it.