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