Create a user define function using SQL Server 2008 R2 - DotNet Brother

In this blog I am trying to explain how create a user define function using SQL Server 2008 R2.

In the SQL Server 2008 R2 have many predefined functions aggregate functions and Scalar functions which are worked for resolve our complex problems, and we are also known the method or functions resolve the complexity.

Aggregate functions:

SQL aggregate functions return a single value, which is calculated from values in a column. Such as

1.       AVG () - Returns the average value.

2.       COUNT () - Returns the number of rows.

Scalar functions:

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

1.       UCASE () - Converts a field to upper case.

2.       LCASE () - Converts a field to lower case.

UDF (User Define Function):

UDF is the concept of User-Defined Functions that allow you to define your own T-SQL functions that can allow zero or more parameters and return a single scalar data value or a table data type.

We can create three types of UDF in SQL Server 2008 R2 and they are:

1.       Scalar

2.       Inline Table-Valued

3.       Multi-statement Table-valued.

Below I am giving an example for User Define Scalar Function which is single parameterizes   function.

Function:

CREATE FUNCTION whichCountry

(@City nvarchar(15))

RETURNS varchar(30)

AS

BEGIN

declare @Return varchar(30)

select @return = case @City

when 'New Delhi' then 'India'

when 'Banglore' then 'India'

when 'Colombo' then 'SriLanka'

when 'New York' then 'U.S.A'

when 'Chicago' then 'U.S.A'

when 'Dhaka' then 'Bangladesh'

when 'Beijing' then 'China'

when 'Kyoto' then 'Japan'

else 'Unknown'

end

return @return

end

Above function will accept a value City name and return its Country name .

Now we will create a table:

Create table tblEmployeeCity

(

    vEmpID varchar(10),

    eName varchar(50),

    vCity varchar(50) 

)

Insert Values in table:

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E001','Ajay Shukla','New York')

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E002','Sanjay Shukla','Beijing')

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E003','Vijay Shukla','Banglore')

 

Use our function:

select Country=dbo.whichCountry(tblEmployeeCity.vCity), tblEmployeeCity.*

from tblEmployeeCity

Above statement will make a virtual column Country’s name and whichCountry () method accepts the value of vCity column and whichCountry () method match the vCity columns values in its code such as if value is Banglore then whichCountry () method will return the India. After that below will show on your screen:

Below I’m using the UDF whichCountry () method during creating a table:

create table tblEmployeeFullInformation

(

  vName varchar(50),

  vCity varchar(15),

  vCountry as (dbo.WhichCountry(vCity))

)

When we will insert values in the tblEmployeeFullInformation table we need only name and Employee Name and its city but country name will automatically get via whichCountry () method.

Insert Values in tblEmployeeFullInformation:

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Vijay Shukla','Banglore')

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Ajay Shukla','New York')

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Sanjay Shukla','Beijing')

Inline table Value:

Its returns a table data type and is an extravagant alternative to a view as the user-defined function, it will take parameters into a T-SQL select command and in synopsis provide us with a parameterized, non-updateable view of the underlying tables.

CREATE FUNCTION EmployeeByCountry

(@Country varchar(30))

RETURNS TABLE

AS

RETURN

  SELECT dbo.WhichCountry(tblEmployeeCity.vCity) as tblEmployeeFullInformation,

         tblEmployeeCity.*

  FROM tblEmployeeCity

  WHERE dbo.WhichCountry(tblEmployeeCity.vCity) = @Country

GO

Above code will create a table with EmployeeByCountry name. And when this table will execute with T-SQL select command then we also need to pass a parameter with the table name below is example:

select * from EmployeeByCountry('India')

Output:

Multi-statement Table-valued.

CREATE FUNCTION Employee

( @ID varchar(50) )

RETURNS

@EmployeeInfo table (

Emp_name Varchar(50),

Emp_City Varchar(20)

)

AS

BEGIN

INSERT INTO @EmployeeInfo SELECT eName,vCity FROM tblEmployeeCity WHERE vEmpID = @ID

 

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @EmployeeInfo VALUES ('','No Enployee Found')

END

 

RETURN

END

GO

Above code will create a virtual table which is shows the employee information as Employee Name and Employee City.

Execute:

SELECT * FROM Employee('E001')