Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 17 February 2012

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 – 

tablequeries

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 – 

scalar1

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

scalartest1

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 – 

scalar2

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

scalartest2

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 – 

tvf1

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

tvftest1

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

tvf2

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

tvftest2
clip_image001

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 – 

clip_image003

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

tvfmultistatementtest

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 DATETIMERETURNS TABLE AS RETURN(
  
SELECT DATEADD(MONTHDATEDIFF(MONTH'19900101'@d), '19900101'AS FirstDayOfMonth
GO
DROP FUNCTION Readers.GetPreviousDay
CREATE FUNCTION Readers.GetPreviousDay(@d DATETIMERETURNS TABLE AS RETURN (
  
SELECT DATEADD(DAYDATEDIFF(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 DATETIMERETURNS TABLE AS RETURN(
  
SELECT DATEADD(DAYDATEDIFF(DAY'19900101'DATEADD(MONTHDATEDIFF(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 DATETIMERETURNS TABLE AS RETURN(
  
SELECT p.PreviousDay AS LastDayOfPreviousMonth
    FROM Readers.GetFirstDayOfMonth(@dAS f
    CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonthAS 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 DATETIMESET @d '20090512'SELECT @d1=LastDayOfPreviousMonth --INTO #t1 FROM Data.Numbers
  CROSS APPLY Readers.GetLastDayOfPreviousMonth_Merged(@d);
GO 

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

DECLARE @d DATETIME@d1 DATETIMESET @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 DATETIMESET @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/