How to write Function in SQL Server 2005

User Defined Functions in SQL Server stops coders write redundant code. There are two types of UDFs:
❑ Those that return a scalar value
❑ Those that return a table

//Create Table
create table t_EmployeeInformation
(
employee_ID int identity(1,1) NOT NULL,
employee_Name varchar(50),
employee_EmailID varchar(20)
)

Following code snippet shows a function that returns a scalar value.

//Create Function
create function f_GetEmployeeId(@employee_EmailID varchar(50))
returns int
as
return
(
select employee_ID from t_EmployeeInformation
where employee_EmailID = @employee_EmailID
)

//Use Function
select dbo.f_GetEmployeeId('khan.rahim@gmail.com')

This creates a function that returns a table.

//Create Function
create function f_GetEmployee(@employee_EmailID varchar(50))
returns table
as
return
(
select employee_ID as "ID", employee_Name as "Name"
from t_EmployeeInformation
where employee_EmailID = @employee_EmailID
)

//Use Function
select * from dbo.f_GetEmployee('khan.rahim@gmail.com')

Thanks
A Rahim Khan

Advertisements
    • Vedha
    • August 17th, 2011

    Thanks nice code….i understood very easily…

  1. Thanks Nice Code……..

    • sunneil
    • July 18th, 2012

    simple but nice explanation………………but my scalar value code did not work..saying “incorrect syntax near Return”

      • Affan Hasan
      • September 27th, 2012

      Try this:

      //Create Function
      create function f_GetEmployeeId(@employee_EmailID varchar(50))
      returns int
      as
      BEGIN
      return
      (
      select employee_ID from t_EmployeeInformation
      where employee_EmailID = @employee_EmailID
      )
      END

    • dili
    • August 16th, 2012

    i’m new to sql
    Can I know where to writ these coding

    • Affan Hasan
    • September 27th, 2012

    Jazakallah, this page reaaaaally helped me in one of ma development task keep up the good work!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: