User Defined functions types and functions and procedures by example

What Kind of User-Defined Functions can I Create?

There are three types of User-Defined functions in SQL Server 2000 and they are

1)Scalar

2)Inline Table-Valued

3)Multi-statement Table-valued.

How do I create and use a Scalar User-Defined Function?

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.

CREATE FUNCTION whichContinent

(@Country nvarchar(15))

RETURNS varchar(30)

AS

BEGIN

declare @Return varchar(30)

select @return = case @Country

when 'Argentina' then 'South America'

when 'Belgium' then 'Europe'

when 'Brazil' then 'South America'

when 'Canada' then 'North America'

when 'Denmark' then 'Europe'

when 'Finland' then 'Europe'

when 'France' then 'Europe'

else 'Unknown'

end

return @return

end

Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name.

print dbo.WhichContinent('USA')

select dbo.WhichContinent(Customers.Country), customers.*

from customers

create table test

(Country varchar(15),

Continent as (dbo.WhichContinent(Country)))

insert into test (country)

values ('USA')

select * from test

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Country          Continent

---------------  ------------------------------

USA              North America

Stored procedures have long given us the ability to pass parameters and get a value back, but the ability to use it in such a variety of different places where you cannot use a stored procedure make this a very powerful database object. Also notice the logic of my function is not exactly brain surgery. But it does encapsulate the business rules for the different continents in one location in my application. If you were to build this logic into T-SQL statements scattered throughout your application and you suddenly noticed that you forgot a country (like I missed Austria!) you would have to make the change in every T-SQL statement where you had used that logic. Now, with the SQL Server User-Defined Function, you can quickly maintain this logic in just one place.

USE Northwind
	GO
	SELECT * FROM Customers
	SELECT * FROM Employees
	SELECT * FROM Orders
	SELECT * FROM [Order Details]
	SELECT * FROM Products
	GO
	--Scalar User Defined Functions Examples
	--Fetch the total number of orders placed by a given customer
	CREATE FUNCTION FetchTotalOrders
	(
	@p_CustomerID NVARCHAR(10)
	) RETURNS INT
	BEGIN
	RETURN (SELECT COUNT(OrderID) FROM Orders
	WHERE CustomerID=@p_CustomerID)
	END
	GO
	--Test the function
	SELECT dbo.FetchTotalOrders('ALFKI') AS 'Total Number Of Orders'
	--Fetch The number of Orders processed by an Employee in a given year
	GO
	CREATE FUNCTION FetchEmployeeProcessedOrdersYearWise
	(
	@p_EmployeeID INT,
	@p_Year INT
	) RETURNS INT
	BEGIN
	RETURN (SELECT COUNT(OrderID) FROM Orders
	WHERE EmployeeID=@p_EmployeeID AND YEAR(OrderDate)=@p_Year)
	END
	GO
	--Test the function
	SELECT dbo.FetchEmployeeProcessedOrdersYearWise(1,1996) AS 'Year 1996'
	SELECT dbo.FetchEmployeeProcessedOrdersYearWise(1,1997) AS 'Year 1997'
	SELECT dbo.FetchEmployeeProcessedOrdersYearWise(1,1998) AS 'Year 1998'
	GO

CREATE FUNCTION dbo.udf_GetProductSales

(

      @Product VARCHAR(10),

      @BeginDate DATETIME,

      @EndDate DATETIME

)

RETURNS MONEY

AS

BEGIN

      DECLARE @Sales MONEY

     

      SELECT @Sales = SUM(SalePrice)

      FROM SalesHistory

      WHERE

            Product = @Product AND 

SaleDate BETWEEN @BeginDate AND @EndDate

      RETURN(@Sales)
END

SELECT dbo.udf_GetProductSales('PoolTable', '1/1/1990', '1/1/2000')

How do I create and use an Inline Table-Value User-Defined Function?

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

CREATE FUNCTION CustomersByContinent

(@Continent varchar(30))

RETURNS TABLE

AS

RETURN

  SELECT dbo.WhichContinent(Customers.Country) as continent,

         customers.*

  FROM customers

  WHERE dbo.WhichContinent(Customers.Country) = @Continent

GO

SELECT * from CustomersbyContinent('North America')

SELECT * from CustomersByContinent('South America')

SELECT * from customersbyContinent('Unknown')

Note that the example uses another function (WhichContinent) to select out the customers specified by the parameter of this function. After creating the user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets. Also note that I do not have to reference the dbo in my reference to this function. However, when using SQL Server built-in functions that return a table, you must now add the prefix :: to the name of the function.

Example from Books Online: Select * from ::fn_helpcollations()

---------------------------------------------------------------------
	--Table Valued Functions Examples [in-Line]
	--Fetch the Product details purchased by a given customer
	CREATE FUNCTION CustomerPurchasedProductDetails
	(
	@p_CustomerID NVARCHAR(10)
	) RETURNS TABLE
	AS
	RETURN
	(
	SELECT P.ProductName,P.UnitPrice,P.UnitsInStock FROM
	Customers C INNER JOIN Orders O
	ON C.CustomerID=O.CustomerID INNER JOIN [Order Details] OD
	ON O.OrderID=OD.OrderID INNER JOIN Products P
	ON OD.ProductID=P.ProductID
	WHERE C.CustomerID=@p_CustomerID
	)
	GO
	--Test the function
	SELECT * FROM dbo.CustomerPurchasedProductDetails('ANTON')
	GO
	--Fetch The Product details processed by an Employee in a given year
	CREATE FUNCTION EmployeeProcessedProductDetails
	(
	@p_EmployeeID INT,
	@p_Year INT
	) RETURNS TABLE
	AS
	RETURN
	(
	SELECT P.ProductName,P.UnitPrice,P.UnitsInStock FROM
	Employees E INNER JOIN Orders O
	ON E.EmployeeID=O.EmployeeID INNER JOIN [Order Details] OD
	ON O.OrderID=OD.OrderID INNER JOIN Products P
	ON OD.ProductID=P.ProductID
	WHERE E.EmployeeID=@p_EmployeeID AND YEAR(O.OrderDate)=@p_Year
	)
	GO
	--Test the function
	SELECT * FROM dbo.EmployeeProcessedProductDetails(1,1996)
	SELECT * FROM dbo.EmployeeProcessedProductDetails(1,1997)
	SELECT * FROM dbo.EmployeeProcessedProductDetails(1,1998)

CREATE FUNCTION dbo.udf_GetProductSalesTable

(

      @Product VARCHAR(10),

      @SaleID INT

)

RETURNS @SalesTable TABLE

(

      SalesTotal MONEY,

      SalesCount INT

)

BEGIN

     

      INSERT INTO @SalesTable(SalesTotal, SalesCount)

      SELECT

            SUM(SalePrice), COUNT(SaleID)

      FROM

            SalesHistory

      WHERE

            Product = @Product AND

            SaleID <= @SaleID

      RETURN

END
GO

SELECT * FROM SalesHistory sh

CROSS APPLY dbo.udf_GetProductSalesTable(sh.Product, sh.SaleID)
ORDER BY sh.SaleID ASC

How do I create and use a Multi-statement Table-Value User-Defined Function?

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

CREATE FUNCTION dbo.customersbycountry ( @Country varchar(15) )

RETURNS

        @CustomersbyCountryTab table (

               [CustomerID] [nchar] (5), [CompanyName] [nvarchar] (40),

               [ContactName] [nvarchar] (30), [ContactTitle] [nvarchar] (30),

               [Address] [nvarchar] (60), [City] [nvarchar] (15),

               [PostalCode] [nvarchar] (10), [Country] [nvarchar] (15),

               [Phone] [nvarchar] (24), [Fax] [nvarchar] (24)

        )

AS

BEGIN

        INSERT INTO @CustomersByCountryTab

        SELECT [CustomerID],

                       [CompanyName],

                       [ContactName],

                       [ContactTitle],

                       [Address],

                       [City],

                       [PostalCode],

                       [Country],

                       [Phone],

                       [Fax]

        FROM [Northwind].[dbo].[Customers]

        WHERE country = @Country

       

        DECLARE @cnt INT

        SELECT @cnt = COUNT(*) FROM @customersbyCountryTab

       

        IF @cnt = 0

               INSERT INTO @CustomersByCountryTab (

                       [CustomerID],

                       [CompanyName],

                       [ContactName],

                       [ContactTitle],

                       [Address],

                       [City],

                       [PostalCode],

                       [Country],

                       [Phone],

                       [Fax]  )

               VALUES ('','No Companies Found','','','','','','','','')

       

        RETURN

END

GO

SELECT * FROM dbo.customersbycountry('USA')

SELECT * FROM dbo.customersbycountry('CANADA')

SELECT * FROM dbo.customersbycountry('ADF')

GO
	--Table Valued Functions Examples [Multi-Statement]
	CREATE FUNCTION CustomerPurchasedProductDetailsMultiStatement
	(
	@p_CustomerID NVARCHAR(10)
	)
	RETURNS @CustomerPurchasedProducts TABLE(
	ProductName NVARCHAR(50),
	UnitPrice DECIMAL(8,2),
	AvailableStock INT)
	AS
	BEGIN
	INSERT @CustomerPurchasedProducts
	SELECT P.ProductName,P.UnitPrice,P.UnitsInStock FROM
	Customers C INNER JOIN Orders O
	ON C.CustomerID=O.CustomerID INNER JOIN [Order Details] OD
	ON O.OrderID=OD.OrderID INNER JOIN Products P
	ON OD.ProductID=P.ProductID
	WHERE C.CustomerID=@p_CustomerID
	RETURN
	END
	GO
	--Test the function
	SELECT * FROM dbo.CustomerPurchasedProductDetailsMultiStatement('ANTON')

What are the benefits of User-Defined Functions?

The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.

limitations of User-Defined Functions?

here are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below – 

• You cannot modify the state of the database using UDFs
• Unlike Stored Procedures, UDF can return only one single result set
• UDF does not support Try-Catch, @ERROR or RAISERROR function

Summary 

 User-defined functions are routines which perform calculations, receive one or more parameters and return either a scalar value or a result set. In this article, we saw how to create User Defined Functions. We also saw how to use Scalar functions and Table Valued Functions [Inline Table Valued Functions and Multi-Statement Table Valued Functions].

procedure:

all fields displayed:

create proc stdmarkdet

as

begin

select * from studentmarknew

end

exec stdmarkdet

select * from studentmarknew

specific fields displayed:

alter proc studentgradedetail(@regno varchar(12))

as

begin

exec stdmarkdet

select distinct registerno,studname,totalmark,grade

from s

where registerno=@regno

end

exec studentgradedetail '10800112101'

database:ashok

local server:192.168.1.63

username:sa

password:sa123

function

1)scalar

2)inline table valued

3)multi statement table valued

2)inline table valued:

alter function stdcourse(@courseid varchar(9))

returns table

as

return

select * From s

where courseid=@courseid

select * from dbo.stdcourse('108390001')

3)multi statement table valued:

alter function studmutltis(@regno varchar(12))

returns @studmark table(registerno varchar(12),totalmark varchar(6),grade varchar(5))

as

begin

insert into @studmark

select registerno,totalmark,grade

from s

where registerno=@regno

return

end

select * from dbo.studmutltis('108001121009')

1)scalar

create function emp_detail(@empid int)

returns varchar(30)

as

begin

declare @return varchar(30)

select @return=case @empid

when '1' then 'emp_name'

when '2' then 'emp_name'

when '3' then 'emp_name'

else 'unknown'

end

return @return

end

print dbo.emp_detail('87878978')

select dbo.emp_detail('2')

User Defined Functions in SQL Server

User Defined Functions play an important role in SQL Server. User Defined functions can be used to perform a complex logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such scenarios, UDFs play an important role. For example, we can call user defined function in a where clause or use a user defined function in a JOIN [Where UDF returns a result set].

SQL Server supports two types of User Defined Functions as mentioned below – 

- Scalar Functions – The function which returns a Scalar/Single value.
- Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as –

• Inline Table
• Multi-statement Table

We will explore these functions today. I am using SQL Server 2012 for this demonstration, although you can use SQL Server 2005, 2008, 2008 R2 as well.

I have preconfigured Northwind database on my SQL Server instance. We will be using the following tables for creating different User Defined Functions –

• Customers
• Employees
• Orders
• Order Details
• Products

Let’s start querying the above table. Open a new Query window and write the following commands – 

Scalar Function

We will now create a scalar function, which returns the number of orders placed by a given customer. Write the following code in your query pad – 



The above function returns an integer value. To test this function, we will write some code as shown below – 



Let us see another example which will fetch the number of orders processed by an employee for a given year. Write the following function in our query pad – 



We will test this function with different years for an employee as shown below – 

Table Valued Functions

Now let’s try an Inline Table valued function. Inline Table valued functions can return a row set using SQL Server Table datatype. You cannot perform addition logic in inline table valued functions. We will fetch the product details purchased by a customer as shown below – 



To test this example we will use a select statement as shown below – 



Another example of the Inline Table Valued Function is as shown below – 



To test this function, we will use different years as shown below – 




We will now see a Multi-Statement Table Valued Function. This function can be used to perform additional logic within the function. The code is as shown below – 



To use the Multi-Statement Table Valued function, use this code – 



There are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below – 

• You cannot modify the state of the database using UDFs
• Unlike Stored Procedures, UDF can return only one single result set
• UDF does not support Try-Catch, @ERROR or RAISERROR function

Many nested inline UDFs are very fast

It is well known that nested calls of scalar UDFs are a huge drag on performance, but in many cases inline UDFs can be nested without performance penalty. Here is a simple repro that you can run and see for yourself. Suppose that you already have two inline UDFs: one returning the previous calendar day and another returning the first day of the month, as follows:

CREATE FUNCTION Readers.GetFirstDayOfMonth(@d DATETIME) RETURNS TABLE AS RETURN(
  SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19900101', @d), '19900101') AS FirstDayOfMonth
) GO
DROP FUNCTION Readers.GetPreviousDay
CREATE FUNCTION Readers.GetPreviousDay(@d DATETIME) RETURNS TABLE AS RETURN (
  SELECT DATEADD(DAY, DATEDIFF(DAY, '19900101', @d) - 1, '19900101') AS PreviousDay
) GO  

Suppose that you need to develop a UDF returning the last day of the previous month. You can develop it from scratch:

CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Merged(@d DATETIME) RETURNS TABLE AS RETURN(
  SELECT DATEADD(DAY, DATEDIFF(DAY, '19900101', DATEADD(MONTH, DATEDIFF(MONTH, '19900101', @d), '19900101')) - 1, '19900101')
    AS LastDayOfPreviousMonth
) 

Alternatively you can reuse your two existing UDFs in several ways, as follows:

CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Nested1(@d DATETIME) RETURNS TABLE AS RETURN(
  SELECT p.PreviousDay AS LastDayOfPreviousMonth
    FROM Readers.GetFirstDayOfMonth(@d) AS f
    CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonth) AS p   
) GO 

SET STATISTICS TIME ON
SET STATISTICS IO ON GO

-- this table has 1 million rows
SELECT COUNT(*) FROM Data.Numbers

DECLARE @d DATETIME, @d1 DATETIME; SET @d = '20090512'; SELECT @d1=LastDayOfPreviousMonth --INTO #t1 FROM Data.Numbers
  CROSS APPLY Readers.GetLastDayOfPreviousMonth_Merged(@d);
GO 

DECLARE @d DATETIME, @d1 DATETIME; SET @d = '20090512'; SELECT @d1=LastDayOfPreviousMonth --INTO #t1 FROM Data.Numbers
  CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested1(@d);
GO

DECLARE @d DATETIME, @d1 DATETIME; SET @d = '20090512'; SELECT @d1=LastDayOfPreviousMonth --INTO #t1 FROM Data.Numbers
  CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested2(@d);
GO

-- this measures the overhead of benchmarking, 
-- scanning the table and assigning a value to a variable DECLARE @d DATETIME, @d1 DATETIME; SET @d = '20090512'; SELECT @d1=@d --INTO #t1 FROM Data.Numbers 

Two examples of UDFs

Before presenting the examples, I will set up my SalesHistory table and load data into it:

IF OBJECT_ID('SalesHistory')>0   

      DROP TABLE SalesHistory;

    





CREATE TABLE [dbo].[SalesHistory]

(         

      [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,         

      [Product] [varchar](10) NULL,               

      [SaleDate] [datetime] NULL,               

      [SalePrice] [money] NULL

)

DECLARE @i SMALLINT

SET @i = 1         

WHILE (@i <=1000)

BEGIN                        

      INSERT INTO SalesHistory(Product, SaleDate, SalePrice)                     

      VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))               

      INSERT INTO SalesHistory(Product, SaleDate, SalePrice)         

      VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                    

      INSERT INTO SalesHistory(Product, SaleDate, SalePrice)             

      VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                           

      SET @i = @i + 1

END
GO

The first UDF I will look at is the scalar-valued UDF. The script below defines a function named dbo.udf_GetProductSales that accepts three parameters and returns a MONEY value. The function uses the three input parameters as criteria in calculating the total sales from the SalesHistory table.

CREATE FUNCTION dbo.udf_GetProductSales

(

      @Product VARCHAR(10),

      @BeginDate DATETIME,

      @EndDate DATETIME

)

RETURNS MONEY

AS

BEGIN

      DECLARE @Sales MONEY

     

      SELECT @Sales = SUM(SalePrice)

      FROM SalesHistory

      WHERE

            Product = @Product AND 

SaleDate BETWEEN @BeginDate AND @EndDate

      RETURN(@Sales)
END

The script below calls the UDF created in the above script. Note: The schema the function belongs to must be used in the call. In this case, the function belongs to the dbo schema.

SELECT dbo.udf_GetProductSales('PoolTable', '1/1/1990', '1/1/2000')

I usually discourage using scalar-valued UDFs in a WHERE criteria statement because, for every record considered in the query, the scalar-valued function will be called. This means that a function used in the WHERE criteria will cause a scan of the values being searched, which is going to be slower than if an index is able to be used. (I will provide more details on this concept in a future article.)

Although the use of a correlated sub-query is sometimes confusing and complicated, the use of them can help solve some of the more challenging query problems. While using these special queries is useful, they only return one column of data. You can use the upgraded table-valued UDFs in SQL Server 2005 to overcome this shortcoming. I'll show you how to use the APPLY operator to accept column values from a table and return a table-result of correlated values.

CREATE FUNCTION dbo.udf_GetProductSalesTable

(

      @Product VARCHAR(10),

      @SaleID INT

)

RETURNS @SalesTable TABLE

(

      SalesTotal MONEY,

      SalesCount INT

)

BEGIN

     

      INSERT INTO @SalesTable(SalesTotal, SalesCount)

      SELECT

            SUM(SalePrice), COUNT(SaleID)

      FROM

            SalesHistory

      WHERE

            Product = @Product AND

            SaleID <= @SaleID

      RETURN

END
GO

The above function accepts the particular product for which we were searching, along with the SaleID from the SalesHistory table. From the function definition, you can see that the function returns a table named @SalesTable that contains two columns: SalesTotal and SalesCount. The body of the function inserts aggregate values into the @SalesTable table variable based upon the input parameters.

The following code uses the APPLY operator to invoke the table-valued function with the values from the SalesHistory table. (Note: Logically, you may want to use a JOIN operator here, but it is not necessary. The APPLY operator essentially does the "JOIN" for us by applying the values from the SalesHistory table to the table-valued function. In a sense, this code works the same way a correlated sub-query does, except that it can return multiple correlated values.)

SELECT * FROM SalesHistory sh

CROSS APPLY dbo.udf_GetProductSalesTable(sh.Product, sh.SaleID)
ORDER BY sh.SaleID ASC

Ref:
https://github.com/sqlservercurry/sql-user-defined-function/blob/master/UDFExamples

http://www.sqlservercurry.com/2014/03/user-defined-functions-in-sql-server_20.html

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/18/many-nested-inline-udfs-are-very-fast.aspx

http://www.techrepublic.com/blog/the-enterprise-cloud/understand-when-to-use-user-defined-functions-in-sql-server/