Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

What is User Defined Function and how to create User Defined Function in SQL Server?

Author: Alanna Kremer

In SQL Server 2000, concept of User Defined Function (UDF) was introduced for users to create their own functions in T-SQL and UDF are available in SQL Server 2005 and 2008.

 

User defined function (UFD) is a code segment written in T-SQL that can accept parameters or it can be without parameters. UDF is database object that contains one or more T-SQL statements. User defined functions can return a single value or they can return table. User defined functions are same like functions provided already in SQL Server. They are created with names, parameters and return values. User Defined functions are created in a database to get data and they can be used in stored procedures, triggers and other User Defined functions. 

 

Comparison between UDF and Stored Procedure
 
User defined Functions are much like stored procedures and views and that is why they are often compared with stored procedures and views. In fact, stored procedures were used in place of User Defined functions before SQL Server 2000. Although stored procedures, views and user defined functions have some similarities but they are different from each other.
 
Advantages of UDF
 
User Defined Functions have their own advantages and disadvantages when compared to stored procedures. The basic advantage of UDF is that it can be used in Select, Where, From, Order By and Case statements. They can be used for repetitive tasks in Select statement and they allow modular programming. User Defined functions can also be used to create joins like views.
 
Disadvantages of UDF
 
User Defined function has more disadvantages than stored procedure. Stored procedures are more flexible than User defined functions. A UDF must return value or a single result set but a stored procured can return a value, a single result set or multiple result sets or it may be created without returning any value. A Non-deterministic function is not allowed to call in a UDF. A UDF stops its execution when error occurs but we can handle errors in stored procedure. We can define output parameters in a stored procedure but in user defined functions, output parameters are not allowed. A UDF cannot call a stored procedure but a stored procedure can call a UDF.
 
Types of User Define Functions
 
There are three types for User Defined Functions.
 
Scalar-valued Functions
Inline functions
Table-valued Functions
 
Scalar-valued Functions
 
Scalar-valued Functions return single value of Integer, String, Date or any other data type except Text, NText, Image, and Timestamp. They are same like built-in functions provided in SQL Server that returns a single value of any data type. A Scalar-valued function can accept one or more parameters. Maximum number of parameters can be 1024.
 
Below is a Scalar-Valued Function.
 
CREATE FUNCTION dbo.GetProductName
       (
       @ProductID int
       )
RETURNS nvarchar(40)
AS
       BEGIN
       DECLARE @ProductName nvarchar(40)
      
       SELECT @ProductName = ProductName
       FROM Products
       WHERE ProductID = @ProductID 
 
       RETURN @ProductName
       END
 
T-SQL statement to call this User Defined Function

SELECT dbo.GetProductName(1) AS ProductName
 
In-Line Functions
 
In-Line function is like view that contains a single Select statement and returns a table. The result of In-Line Function can be used in join queries because it returns a table of values. In-Line Functions are only restricted to single select statement and it cannot contain any complex logic in it.
 
Below is an In-Line Function
 
CREATE FUNCTION dbo.GetProductsByCategoryID
       (
       @CategoryID int,
       @UnitPrice money
       )
RETURNS TABLE
AS
       RETURN
       SELECT *
       FROM Products
       WHERE CategoryID = @CategoryID
       AND UnitPrice <= @UnitPrice
 
 
T-SQL statement to call this User Defined Function
 
SELECT        ProductID, ProductName, UnitPrice
FROM            dbo.GetProductsByCategoryID(1, 10) AS GetProductsByCategory
 
Table-valued Function
 
It is also called multi-statement table-valued function. It can contain multiple T-SQL statements like stored procedure and in contrast to In-Line functions that contain only one statement. Multi-statement Table-valued function can perform any kind of loop or conditional processing.
 
Below is a Table-valued Function.
 
CREATE FUNCTION dbo.Fun_EmployeeTerritories
       (
       @EmployeeID int
       )
RETURNS @Employee_Territories TABLE
(
LastName nvarchar(20),
FirstName nvarchar(10),
TerritoryID nvarchar(20),
TerritoryDescription nchar(50)
)
AS
       BEGIN
 
       DECLARE
       @LastName nvarchar(20),
       @FirstName nvarchar(10),
       @TerritoryID nvarchar(20),
       @TerritoryDescription nchar(50)
 
       SELECT
       @LastName = LastName,
       @FirstName = FirstName,
       @TerritoryID = T.TerritoryID,
       @TerritoryDescription = TerritoryDescription
       FROM  Employees E INNER JOIN
       EmployeeTerritories ET ON E.EmployeeID = ET.EmployeeID
       CROSS JOIN Territories T
       WHERE
       E.EmployeeID = @EmployeeID
      
 
              BEGIN
                     INSERT INTO @Employee_Territories
                     SELECT @FirstName, @LastName, @TerritoryID, @TerritoryDescription
              END
       RETURN
       END
 
 
T-SQL statement to call this User Defined Function
 
SELECT        *
FROM            dbo.Fun_EmployeeTerritories(2) AS EmployeeTerritories
 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< What is Replication in SQL Server?

Disclaimer - Privacy
© 2002-2017 DevASP.net