USER DEFINED FUNCTIONS IN SQL SERVER 2005

1.Functions are SQL SERVER objects which are used to write logic for funtionalities other than business logic.

2.Functions by default does not return a value.

3.Functions can’t be used for insert , update or delete.

4.Functions do not have concept of INPUT and OUTPUT Parameters.

5.There are three types of functions available in the user defined functions.

a.Scalar valued functions : They will return single value.

b.Inline table valued functions : They always returns a resultset
but we cannot define a new structure of resultset.It cannot return a
single value.

c.Multivalued table functions: They returns a resultant table.Here we can define a new structure for the table.

6.Each function is used for specific purpose.

7.We can do all the things with stored procedures as well as functions but what we write is purely depends on your requirements.

Examples of scalar valued functions.

Create function
(

@param1 ,@param2 ….param list

)

returns

AS

BEGIN

SQL Statements.
returns variable name –>Datatype of this variable should be same as what we are returning.

END

Eg.
Create function func_avg
( @cat_id int )
returns money
AS
BEGIN

Declare @avg as money
SELECT @avg = agv(UnitPrice) from products where categoryId = @catId
return @avg

END

How to execute : select * from dbo.func_avg(1)

Inline table Value functions

Create function

(

@param1 , @param2 …..param list

)
RETURNS TABLE
AS
RETURN
SQL STATEMENT

Create function func_dtls
(
@catId INT
)
Returns table
as
Return

SELECT prodName , CategoryId , UnitPrice from Products
Where categoryId = @catId

Get result….

Select * from func_dtls(23)

Table valued functions

Create function
(

@param1 , @param2 …..param list

)
Returns @tableName TABLE ()
AS
BEGIN
SQL STATEMENT
RETURN
END

eg.
Create function func_temp
(

)
returns @newTable TABLE
(

cust_id int indentity ( 1,1 ),
cname varchar(60),
countryName varchar(30)

)

AS
BEGIN

INSERT @newTable
SELECT CompanyName , Country from customers
Return

END

Pawan Kumar
Pawankkmr@hotmail.com