Hello All, We are going to start new batch from next week. message/call or mail us for more details.

8 September 2013

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')

Create a user define function using SQL Server 2008 R2

 

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:


Create a user define function using SQL Server 2008 R2


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')

Create a user define function using SQL Server 2008 R2

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:

Create a user define function using SQL Server 2008 R2

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')

Create a user define function using SQL Server 2008 R2

No comments:

Post a Comment