User Defined Functions

User Defined Functions

A User-Defined Function is, much like a Stored Procedure, an ordered set of T-SQL statements that are pre-optimized and compiled and can be called to work as a single unit. The primary difference between them is how results are retuned.
With a Stored Procedure, you can pas parameters in, and also get values in parameters passed back out. You can return a value, but that value is really intended to indicate success or failure rather than return data. You can also return result sets, but you can’t really use those result sets in a query without first inserting them into some kind of table (usually a temporary table) to work with them further.
With a UDF, however, you can pass parameters in, but not out. Instead, the concept of output parameters has been replaced with a much more robust return value. As with system functions, you can return a scalar value. Another advantage is that this value is not limited to just the integer data type as it would be for a Stored Procedure. Instead, you can return most SQL Server data types.

Types of UDF’s:

  • Those that return a scalar value
  • Those that return a table

General Syntax for creating a UDF:

CREATE FUNCTION function_name
(<@parameter name> <data type>[=default value>][,……n])
RETURNS {<data type>|TABLE}
          [<function statements>]
{RETURN <type as defined in RETURNS clause> |

            RETURN (<SELECT statement>)}



UDFs Returning a Scalar Value:

          Much like SQL Server’s own built-in functions, they will return a scalar value to the calling script or procedure. One of the truly great things about a UDF is that you are not limited to an integer for a return value – instead, it can be of any valid SQL Server data type, except for BLOBs, cursors, and timestamps. Even if you wanted to return an integer, a UDF should be advantage to you for two different reasons:

1.      Unlike Stored Procedures, the whole purpose of the return value is to serve a meaningful piece of data – for Stored Procedures, a return value is meant as an indication of success or failure, and, in the event of failure, to provide some specific information about the nature of that failure.

2.      You can perform functions in-line to your queries (for instances, include it as part of your SELECT statement) – you can’t do that with a Stored Procedure.

Case study:

Let’s create a table called ORDERS with 3 columns named as OrderID, CustID and OrderDate as follows

CREATE TABLE orders(ordered int, custid int,orderdate datetime)

Lets insert some data into the table with the use of a simple T-SQL program.

DECLARE @Counter int

SET @Counter =1
WHILE @Counter <= 10
          INSERT INTO Orders
                   VALUES(1,1,DATEADD(mi, @Counter, GETDATE()))
          SET @Counter = @Counter + 1


So, this gets us 10 rows inserted, with each row being inserted with today’s date, but one minute apart from each other.

So, now we’re ready to run a simple query to see what orders we have today. We might try something like:

FROM Orders
WHERE orderdate=GETDATE()

Unfortunately, this query will not get us anything back at all. This is because GETDATE() gets the current time not just the day.

The solution is to convert the date to a string and back in order to truncate the time information, then perform comparison.

FROM Orders
WHERE CONVERT(varchar(12), orderdate, 101)=CONVERT(varchar(12), GETDATE(),101)

This time, we will get back every row with today’s date in the OrderDate column – regardless of what time of day the order was taken. Unfortunately, this isn’t exactly the most readable code. Imagine you had a large series of dates you needed to perform such comparisons against – it can get very ugly indeed.

So now let’s look at doing the same thing with a simple user-defined function. First, we’ll need to create the actual function. This is done with the new CREATE FUNCTION command, and it’s formatted much like a Stored Procedure. For example, we might code this function like this:

CREATE FUNCTION dbo.DateOnly(@dt datetime)
RETURNS varchar(12)
          RETURN CONVERT(varchar(12), @dt, 101)

Whether the date returned from GETDATE() is passed in as the parameter and the task of converting the date is included in the function body and the truncated date is returned.

To see this function in action, let’s reformat our query as follows:

FROM  Orders
WHERE dbo.DateOnly(OrderDate) = dbo.DateOnly(GETDATE())

We get back the same set as with the stand-alone query. Even for a simple query like this one, the new code is quite a bit more readable. There is, however, one requirement for this type. The owner name is required in the function call. SQL Server will, for some reason, not resolve functions the way it does with other objects.

UDFs that Return a Table:

SQL Server’s new user-defined functions are not limited to just returning scalar values. They can return something far more interesting – tables.

To make the change to using a table, as a return value is not hard at all – a table is just like any other SQL Server data type as far as a UDF is concerned. To illustrate this, we’ll build a relatively simple one:

USE pubs

CREATE FUNCTION dbo.fnListOfAuthors()
                             au_lname + ‘, ‘ + au_fname AS au_name,
                             address AS address1,
                             City + ‘, ‘ + state + ‘ ‘ + zip AS address2
                   FROM authors)

This function returns a table of SELECTEed records and does a little formatting: joining the last and first names, separating them with a comma, and concatenating the three components to fill the address2 column.

At this point, we’re ready to use our function just as we would use a table – the only exception is that as was discussed with scalar functions, we must use the two-part naming convention:

FROM dbo.fnListOfAuthors()


Post a Comment