Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 363

UDFs are not allowed to have any side effects. This obviously means that UDFs are not allowed 

to apply any schema or data changes in the database. But other ways of causing side effects are less 

obvious. For example, invoking the RAND function to return a random value or the NEWID function 

to return a globally unique identifier (GUID) has side effects. Whenever you invoke the RAND func-

tion without specifying a seed, SQL Server generates a random seed that is based on the previous 

invocation of RAND. For this reason, SQL Server needs to store information internally whenever you 

invoke the RAND function. Similarly, whenever you invoke the NEWID function, the system needs to 

set some information aside to be taken into consideration in the next invocation of NEWID. Because 

RAND and NEWID have side effects, you’re not allowed to use them in your UDFs.

For example, the following code creates a UDF called dbo.GetAge that returns the age of a person 

with a specified birth date (@birthdate argument) at a specified event date (@eventdate argument).

IF OBJECT_ID('dbo.GetAge') IS NOT NULL DROP FUNCTION dbo.GetAge; 

GO 

 

CREATE FUNCTION dbo.GetAge 

( 

  @birthdate AS DATE, 

  @eventdate AS DATE 

) 

RETURNS INT 

AS 

BEGIN 

  RETURN 

    DATEDIFF(year, @birthdate, @eventdate) 

    - CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate) 

              < 100 * MONTH(@birthdate) + DAY(@birthdate) 

           THEN 1 ELSE 0 

      END; 

END; 

GO

The function calculates the age as the difference, in terms of years, between the birth year and 

the event year, minus 1 year in cases for which the year, the event month, and the day are smaller 

than the birth month and day. The expression 100 * month + day is simply a trick to concatenate the 

month and day. For example, for the twelfth day in the month of February, the expression yields the 

integer 212.

Note that a function can have more than just a RETURN clause in its body. It can have code with 

flow elements, calculations, and more. But the function must have a RETURN clause that returns a 

value. 

To demonstrate using a UDF in a query, the following code queries the HR.Employees table and 

invokes the GetAge function in the SELECT list to calculate the age of each employee today.

SELECT 

  empid, firstname, lastname, birthdate, 

  dbo.GetAge(birthdate, SYSDATETIME()) AS age 

FROM HR.Employees;

www.it-ebooks.info

---

364  

Microsoft SQL Server 2012 T-SQL Fundamentals

For example, if you were to run this query on February 12, 2012, you would get the following 

output.

empid       firstname  lastname             birthdate                 age 

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

1           Sara       Davis                1958-12-08 00:00:00.000   53 

2           Don        Funk                 1962-02-19 00:00:00.000   49 

3           Judy       Lew                  1973-08-30 00:00:00.000   38 

4           Yael       Peled                1947-09-19 00:00:00.000   64 

5           Sven       Buck                 1965-03-04 00:00:00.000   46 

6           Paul       Suurs                1973-07-02 00:00:00.000   38 

7           Russell    King                 1970-05-29 00:00:00.000   41 

8           Maria      Cameron              1968-01-09 00:00:00.000   44 

9           Zoya       Dolgopyatova         1976-01-27 00:00:00.000   36 

 

(9 row(s) affected)

Note that if you run the query in your system, the values that you get in the age column depend 

on the date on which you run the query.