Functions in SQL Server 2005

Functions in SQL Server are very useful.There are different types of system functions are there.apart from System functions we can create user defined functions.
User defined functions are also very useful. Functions provide more flexibility.we can  use functions in Triggers, procedures including in select,insert,update statements.
Functions must return a variable.There are two types of functions are there.

system functions
user defined functions.

System functions are predefined functions in SQL Server.when ever SQL Server installations.
there are different types of system functions are there.Some of the functions most used are Aggregate  functions.Date functions.Math functions etc.

user defined functions provides flexibility. we can write our own user defined  and can be used in any where in statements,functions,procedures.there are two types of user defined functions in SQL server 2005.

Table-valued functions
Scalar-valued functions

Table-valued functions used for returns an output as table.

where as Scalar-valued functions returns a single datatype(int, varchar) as output.

Here is a sample of function of a scalar valued functions.Which gives the multiplication of two numbers.


scalar-valued function Example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION multiplicationoftwonumbers
(
@num1 int,@num2 int
)
RETURNS int
AS
BEGIN

DECLARE @result bigint

SELECT @result=(@num1*@num2);


RETURN @result

END
GO

Then after complete the execution above script .

we can call the function as

 select dbo.multiplicationoftwonumbers(5,2)

where @num1=5 and @num2=2;

then it will give the result as 10.

This is simplest example of writing a scalar value function.

The second one is table valued function

 the below example shows simple Table value function.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION getMandal
(
@Dist int
)
RETURNS TABLE
AS
RETURN
(

select * from HIHLMain.Mandal where DistrictId=@Dist
)
GO


The above gives select all the Mandal for a particular District.

usage:

Select *  from getmandal(6)

which gives the output of all mandal details based on the Given District.