Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 15 February 2013

UDF Examples Useful



UDF Examples
The following scalar function returns a maximum amount of books sold for a specified title. This function should be executed in pubs database. If the title has no sales, the UDF will return zero.
/* scalar function to return max amount of books sold
** for the specified title_id
*/

CREATE FUNCTION dbo.udf_max_copies_sold_for_title (@title_id CHAR(6))
RETURNS INT
AS
BEGIN
DECLARE @qty INT
-- initialize the variable at 0:
SELECT @qty = 0

SELECT
    @qty = MAX(qty)
FROM sales
WHERE
title_id = @title_id

/* If there are no books sold for title_id specified
** then return 0:
*/
RETURN ISNULL(@qty, 0)

END
Now we can execute this function as follows:
SELECT dbo.udf_max_copies_sold_for_title ('bu2075')
Results:
-----------
55
The following in-line function returns the product name, quantity ordered, and supplier for the top five best sellers in a particular year in the Northwind database:
/* in-line function to return quantity, product name and supplier
** for the top 5 best-selling products within the specified year
*/

CREATE FUNCTION dbo.udf_top_5_best_sellers (@year INT)
RETURNS TABLE
AS
RETURN

SELECT TOP 5
     SUM(quantity) AS quantity_ordered,
     a.ProductID,
     ProductName,
     CompanyName AS Supplier
FROM [order details] a INNER JOIN products b
   ON a.productid = b.productid
   INNER JOIN suppliers c ON c.supplierid = b.supplierid
   INNER JOIN orders d ON d.orderid = a.orderid
      AND DATEPART(YEAR, OrderDate) = @year
GROUP BY a.productid, productname, CompanyName
ORDER BY 1 DESC
We can execute this function as follows:
SELECT * FROM dbo.udf_top_5_best_sellers (1998)
Results:
quantity_ordered
ProductID
ProductName
Supplier
659
13
Konbu
Mayumi's
546
24
Guaraná Fantástica
Refrescos Americanas LTDA
542
60
Camembert Pierrot
Gai pturage
513
59
Raclette Courdavault
Gai pturage
414
7
Uncle Bob's Organic Dried Pears
Grandma Kelly's Homestead

Alternatively, we can SELECT only the desired columns from this function:
SELECT ProductID,
        ProductName
FROM dbo.udf_top_5_best_sellers (1998)
Results:
ProductID
ProductName
13
Konbu
24
Guaraná Fantástica
60
Camembert Pierrot
59
Raclette Courdavault
7
Uncle Bob's Organic Dried Pears

The following multi-statement function accepts a delimited list as a parameter parses the string and returns the table containing each of the values in the parameter.
/* multi-statement function to parse
** a delimited list and turn it into a rowset
** DEFAULT delimiter is comma
*/

CREATE FUNCTION dbo.parse_comma_delimited_integer(
    @list    VARCHAR(8000),
    @delimiter VARCHAR(10) = ',')

-- table variable that will contain values
RETURNS @tablevalues TABLE (
    item INT)
AS
BEGIN
  DECLARE @item VARCHAR(255)

  /* Loop over the commadelimited list */
  WHILE (DATALENGTH(@list) > 0)
    BEGIN
      IF CHARINDEX(@delimiter,@list) > 0
        BEGIN
          SELECT @item =
             SUBSTRING(@list,1,(CHARINDEX(@delimiter, @list)-1))
          SELECT @list =
                SUBSTRING(@list,(CHARINDEX(@delimiter, @list) +
                DATALENGTH(@delimiter)),DATALENGTH(@list))
        END

     ELSE
        BEGIN
          SELECT @item = @list
          SELECT @list = NULL
        END

   -- Insert each item into temp table
    INSERT @tablevalues (
        item)
    SELECT item = CONVERT(INT, @item)
   END

 RETURN
END
Now we can execute this function as follows:
SELECT * FROM dbo.parse_comma_delimited_integer
('39, 549, 324, 3556, 24, 2132, 345', ',')
Results:
item
39
549
324
3556
24
2132
345

The following multi-statement UDF written for the Northwind database finds customers who have bought products in large quantities during a given year. The UDF accepts the year and amount of total sales and returns the customer name, the product that they have bought in large quantities, and all sales representatives who interfaced with these customers within the given year:
CREATE FUNCTION dbo.udf_top_customers_and_reps (
  @year INT,
  @amount INT)

RETURNS
  @temp TABLE(
    ProductName VARCHAR(200),
    CategoryName VARCHAR(200),
    CustomerID CHAR(5),
    CompanyName VARCHAR(200),
    TotalSales INT,
    EmployeeNames VARCHAR(2000))

AS
BEGIN

/* populate the temp table with customers that have purchased any product
* with total sales greater than the specified amount, within the given year
*/

INSERT @temp (
     ProductName ,
     CategoryName ,
     CompanyName ,
     CustomerID ,
     TotalSales
)


SELECT
    ProductName,
    CategoryName,
    CompanyName,
    b.CustomerID,
    SUM(a.UnitPrice * quantity) AS total_sales
FROM [order details] a
INNER JOIN orders b ON a.orderid = b.orderid
INNER JOIN products c ON c.productid = a.productid
INNER JOIN customers d ON d.customerid = b.customerid
INNER JOIN categories e ON e.CategoryID = c.CategoryID
WHERE DATEPART(YEAR, OrderDate) = @year
GROUP BY c.ProductName, e.CategoryName, b.CustomerID,
d.CompanyName, DATEPART(YEAR, OrderDate)
HAVING SUM(a.UnitPrice * quantity) > @amount
ORDER BY ProductName

/* now get all the employees that have been involved with the customers
* in the given year and return them in a comma-delimited list
*/


DECLARE @CustomerID CHAR(5),
    @EmployeeName VARCHAR(200)

DECLARE @Employees TABLE (
        EmployeeName VARCHAR(80))

DECLARE CustomerCursor CURSOR FOR
SELECT CustomerID
FROM @temp a

OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor INTO @CustomerID

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT @employees
SELECT DISTINCT FirstName + ' ' + LastName
 FROM Employees a INNER JOIN Orders b ON a.EmployeeID = b.EmployeeID
 AND DATEPART(YEAR,OrderDate) = @year
 WHERE b.CustomerID = @CustomerID


/* create a comma-delimited list of employees */

SELECT @EmployeeName = ''
 SELECT @EmployeeName = @EmployeeName + ', ' + EmployeeName
 FROM @Employees      

SELECT @EmployeeName = SUBSTRING(@EmployeeName, 3, LEN(@EmployeeName)-2)



UPDATE @temp
SET EmployeeNames = @EmployeeName
WHERE CustomerID = @CustomerID

DELETE @Employees
FETCH NEXT FROM CustomerCursor INTO @CustomerID
END
CLOSE CustomerCursor
DEALLOCATE CustomerCursor


RETURN
END
The following execution of the function returns customers who have bought any product in amounts of over $10,000 during 1997:
SELECT * FROM dbo.udf_top_customers_and_reps (1997, 10000)
Results:
ProductName
CategoryName
CustomerID
Companyname
TotalSales
EmployeeNames
Côte de Blaye
Beverages
MEREP
Mère Paillarde
10329
Janet Leverling, Laura Callahan, Margaret Peacock, Michael Suyama, Nancy Davolio, Robert King
Côte de Blaye
Beverages
SIMOB
Simons bistro
10540
Andrew Fuller, Margaret Peacock, Robert King
SQL Server 2000 supports User-Defined Functions (UDFs) - one or more Transact-SQL statements that can be used to encapsulate code for reuse. User-defined functions cannot make a permanent changes to the data or modify database tables. UDF can change only local objects for this UDF, such as local cursors or variables.
There are three types of UDF in SQL Server 2000:
·  Scalar functions
·  Inline table-valued functions
·  Multistatement table-valued functions
Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.
Inline table-valued functions return the result set of a single SELECT statement.
Multistatement table-valued functions return a table, that was built with many TRANSACT-SQL statements.
User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.

UDF examples
Database creation date
This UDF will return the creation date for a given database (you should specify database name as parameter for this UDF):
CREATE FUNCTION dbo.DBCreationDate
  ( @dbname sysname )
RETURNS datetime
AS
BEGIN
  DECLARE @crdate datetime
  SELECT @crdate = crdate FROM master.dbo.sysdatabases
    WHERE name = @dbname
  RETURN ( @crdate )
END
GO
This is the example for use:
SELECT dbo.DBCreationDate('pubs')
GO

Date the object was created
This UDF will return the creation date for a given object in the current database:
CREATE FUNCTION dbo.ObjCreationDate
  ( @objname sysname)
RETURNS datetime
AS
BEGIN
  DECLARE @crdate datetime
  SELECT @crdate = crdate FROM sysobjects WHERE name = @objname
  RETURN ( @crdate )
END
GO
This is the example for use:
SELECT dbo.ObjCreationDate('authors')
GO

Get date part of datetime value
This UDF will return the date part of datetime value:
CREATE FUNCTION dbo.DatePart
  ( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
  RETURN ( CONVERT(varchar(10),@fDate,101) )
END
GO
This is the example for use:
SELECT dbo.DatePart('11/11/2000 11:15AM')
GO

Get time part of datetime value
This UDF will return the time part of datetime value:
CREATE FUNCTION dbo.TimePart
  ( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
  RETURN ( CONVERT(varchar(7),right(@fDate,7),101) )
END
GO
This is the example for use:
SELECT dbo.TimePart('11/11/2000 11:15AM')
GO

Get the number of working days between two dates
This UDF will return the number of working days between two dates (not including these dates):
CREATE FUNCTION dbo.GetWorkingDays
  ( @StartDate datetime,
    @EndDate datetime )
RETURNS INT
AS
BEGIN
  DECLARE @WorkDays int, @FirstPart int
  DECLARE @FirstNum int, @TotalDays int
  DECLARE @LastNum int, @LastPart int
  IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
    BEGIN
      RETURN ( 0 )
    END
  SELECT
   @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
   @FirstPart = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 6
                 WHEN 'Monday' THEN 5
                 WHEN 'Tuesday' THEN 4
                 WHEN 'Wednesday' THEN 3
                 WHEN 'Thursday' THEN 2
                 WHEN 'Friday' THEN 1
                 WHEN 'Saturday' THEN 0
               END,
   @FirstNum = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 5
                 WHEN 'Monday' THEN 4
                 WHEN 'Tuesday' THEN 3
                 WHEN 'Wednesday' THEN 2
                 WHEN 'Thursday' THEN 1
                 WHEN 'Friday' THEN 0
                 WHEN 'Saturday' THEN 0
               END
  IF (@TotalDays < @FirstPart)
     BEGIN
       SELECT @WorkDays = @TotalDays
     END
  ELSE
     BEGIN
       SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
       SELECT @LastPart = (@TotalDays - @FirstPart) % 7
       SELECT @LastNum = CASE
         WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
         ELSE 0
       END
       SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
     END
  RETURN ( @WorkDays )
END
GO
This is the example for use:
SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')
GO