What is function in SQL Server 2005/2008 ?

What is function in SQL Server 2005/2008 ?

Function is a database object in Sql Server. It is a set of sql statements that accepts only input parameters, perform actions and return the result.

 Function can return only single value or a table. We can’t use function to Insert, Update, and Delete records in the database table.

Basically two types of function in SQL Server.

1. System Defined Function.

2. User Defined Function

System Defined Function derived in two types.

1. System Defined Function.

1. Scalar Function

SQL scalar functions return a single value, based on the input value.

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

2. Aggregate Function

SQL aggregate functions return a single value, calculated from values in a column.

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

2. User Defined Function

User Defined Function derived in three types.

1. Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any data type value from function.

sno	city
1	Delhi
2	Kanpur
3	Lucknow
4	Aligarh
5	Alur
6	Ambaliyasan
7	Anandnagaar

select * from City

select * from State

sno	city	state
1	Lucknow	UP
2	Kanpur	UP
3	Anandnagaar	Assam
4	Ambaliyasan	Gujarat
5	Ambaji	Gujarat
6	Aligarh	UP
7	Delhi	Delhi
8	Alandha	Karnataka
9	Achhnera	UP

create function [dbo].[functionStatecity]

(

@state nvarchar(50),@city nvarchar(50)

)

returns nvarchar(250)

As

Begin return (Select @state + ' _ '+ @city);

end

Calling function in query:

Select dbo.functionStatecity(state,city) as Name, sno from state

Name	sno
UP _ Lucknow	1
UP _ Kanpur	2
Assam _ Anandnagaar	3
Gujarat _ Ambaliyasan	4
Gujarat _ Ambaji	5
UP _ Aligarh	6
Delhi _ Delhi	7
Karnataka _ Alandha	8
UP _ Achhnera	9

2. Inline Table Value Function

User defined Inline Table Value function return a table variable as a result of actions performs by function.

create function [dbo].[functioncity]()

returns Table

As

return (Select * from city where sno='4')

Calling function in query:

Select * from functioncity()

sno	city
4	Aligarh

3. Multi-Statement Table Value Function

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 stateme