Function

• A function is a database object in SQL Server.
• it is a set of SQL statements that accept only input parameters, Return the result only a single value or a table (Result Set format)
• Column Name Must Be unique (No duplicate column names are allowed)
• User Defined Function doesn't support Exception handling , and transaction (begin Tran / commit Tran)
• We can’t use a function to Insert, Update, Delete records in the database tables. 
• If we Use Insert/Update/Delete in Above function then it gives following error.
  Invalid use of a side-effecting operator 'DELETE' within a function. hence function cannot call stored procedure.
• we can do  Insert, Update, Delete DML operation on Table variable, but not on physical table.

2 Types

User Defined function  (UDF) - user can create function.

    3 Types of UDF

    1) Scalar Valued Function
    2) Table Valued Function
    3) Multi statement Table Valued Function

System Defined function - already available in system - SQL Server.

different types of it,
    

Aggregate function
Sum(), Min(), Max(), Count(), Avg()

Date and Time function
Getdate(), Day(), Year(), Month()

String Function 
Lower()  Upper()

User Defined function  (UDF) - user can create function.

    

    1) Scalar Valued Function - 

A Scalar UDF can accept 0 to many input parameter and will return a single value.

Return Datatype like Int, char, varchar etc.

Text, ntext, image and timestamp data types are not supported.

example-

1)

Create Function NumberCube(@a Int)

Returns Int

As

Begin

Declare @cube int

Set @cube=@a*@a*@a

Return @cube

End

Select dbo.NumberCube(2) 

output - 8
2)
Create Function [dbo].[fn_concatstr]
(
@firstName varchar(50),
@LastName varchar(50)
)
Returns varchar(100)
As
Begin
Declare @Fullname varchar(100)
Set @Fullname= @firstName+'  '+@LastName
Return @Fullname
End

Select [dbo].[fn_concatstr] 'Kishor','Sonawane' 

output- Kishor Sonawane

2) Table Valued Function-

Return value is derived from the SELECT statement
No BEGIN/END block needed in the CREATE FUNCTION statement. 
No need to specify the table variable name (or column definitions for the table variable)

Create Function [dbo].[fn_PatientListByCity]

(

@CityId Int

)

Returns Table

As

Return

SELECT [PatientID]

      ,[FirstName]

      ,[MiddleName]

      ,[LastName]

      ,C.Name As CityName

  FROM [HIMS].[dbo].[Patient] P

  Inner Join City C

  On P.CityID=C.CityID

  Where P.CityID=@CityId

Select * from [dbo].[fn_PatientListByCity](1)

3) Multi statement Table Valued Function

Returns a table variable as a result of actions perform by function.

In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

It can have one or more than one T-Sql statement.

Within the create function Query you must define the table structure that is being returned.

Create function fn_PatientListByCity_Multi

(

@CityId Int

)

returns @Patient Table(PatientID int, FirstName varchar(50),LastName varchar(50),FullName varchar(50),CityName varchar(50))

As

begin

Insert into @Patient

SELECT [PatientID]

   ,[FirstName]

   ,[LastName]

   ,'' as FullName

   ,C.Name As CityName

  FROM [HIMS].[dbo].[Patient] P

  Inner Join City C

  On P.CityID=C.CityID

  Where P.CityID=@CityId

  Update @Patient Set FullName=[FirstName]+' '+[LastName] 

  --It will update only in @Patient table not in Original table

return

end 

Select * from [dbo].[fn_PatientListByCity_Multi](1)

we cant use temp table #tmp in user defined function.

we can join function with other tables based on column.

we call to function using select statement.