Powered by Blogger.

Saturday, February 15, 2014

User Defined Function example in SQL Server 2008

In this post we will check one user defined function in SQL Server 2008.

You can also check some useful posts like:

- Get all user defined tables in SQL Server 2008

- view in sql server 2008

- Difference between primary key and foreign key in SQL server

User defined functions can be created by users.

Below is a function that will take one input parameter and also returns one integer value.

Here it will take the empid and returns the contact number for the particular empid.

Create function EmployeeContact(@Empid int)
returns int

declare @result int
select @result=EmloyeeDetail.ContactNo from EmloyeeDetail where EmloyeeDetail.Empid=@Empid

return @result


You can call the function like below
Select dbo.EmployeeContact(1) as ContactNumber from EmloyeeDetail

This will return the contact number of empid =1.

Here are some points to remember about functions:
- Function returns only single value.

- Function accepts only input parameters.

- Function can not be used to Insert, Update, Delete data in database table.

- Function doesn't support Exception handling.