Types of User-Defined Functions According to Their Return Value
You can define a user-defined function with a single statement or with multiple statements, as you will see later in this chapter in the "Creating and Dropping User-Defined Functions" section.
According to their return value, user-defined functions can be divided into three groups:
Scalar functions that return a single scalar value.
Table-valued functions that return a full result set, similar to a table.
Inline user-defined functions are a special case of table-valued user-defined functions, but they are limited to a single SELECT statement.
Scalar Functions
Scalar user-defined functions return a single value, and they can be used wherever an expression is accepted, such as
In the SELECT clause of a SELECT statement, as a part of an expression or as an individual column
In the SET clause of an UPDATE statement, as a value to insert into a field of the table being updated
In the FROM clause of any DML statement (SELECT, UPDATE, INSERT, DELETE), as a single-column, single-row result set–derived table
In the FROM clause of any DML statement, as part of the joining conditions in the ON clause
In the WHERE clause or HAVING clause of any DML statement
In the GROUP BY clause, as part of any grouping condition
In the ORDER BY clause of any statement, as sorting criteria
As a DEFAULT value for a column
Inside a CHECK CONSTRAINT definition
In a PRINT statement, if the user-defined function returns a string
As part of the condition of IF or WHILE statements
As part of the definition of a compute column
As a parameter to call a stored procedure or another user-defined function
As a return value of a stored procedure, if the user-defined function returns an integer value
As a return value of another scalar user-defined function
Scalar user-defined functions can be combined with other functions in an expression, as long as the data types are compatible with the operation.
Tip
You can identify scalar user-defined functions because they return a scalar data type and their definition is enclosed in a BEGIN...END block.
Creating Scalar User-Defined Functions
To create a scalar user-defined function, you must use the CREATE FUNCTION statement, as shown in Listing 10.4.
The first example creates the MaxProductID function, which selects the maximum available ProductID from the Products table. This function returns a scalar integer value.
The second example creates the WhoWhere function, which returns a scalar string with information about which is the login of the connected user and from which machine the user is connected.
The third example returns the date of the latest executed process in SQL Server, which we can consider today's date in general.
The fourth example is a bit more complex; it generates a random number, between 0.0 and 1.0, based on the time of the latest statement executed in SQL Server. This function is similar to the system function Rand; however, Rand cannot be used inside a user-defined function, whereas this PRand function can be used.
Code Listing 10.4. Creating Scalar User-Defined Functions
|
USE Northwind
GO
-- Returns the maximum ProductID from Products
CREATE FUNCTION dbo.MaxProductID
()
RETURNS int
AS
BEGIN
RETURN (
SELECT MAX(ProductID)
FROM dbo.Products
)
END
GO
-- Returns who and from where the query is executed
CREATE FUNCTION dbo.WhoWhere
()
RETURNS nvarchar(256)
AS
BEGIN
RETURN SYSTEM_USER
+ 'FROM '
+ APP_NAME()
END
GO
-- Returns the date of the latest executed statement
-- which is usually today
CREATE FUNCTION dbo.Today
()
RETURNS smalldatetime
AS
BEGIN
DECLARE @sdt smalldatetime
SELECT @SDT = CONVERT(varchar(10), MAX(last_batch), 112)
FROM master.dbo.sysprocesses
RETURN @SDT
END
GO
-- Function that produces a non-predictable
-- Pseudo-Random Number
CREATE FUNCTION dbo.PRand
()
RETURNS float
AS
BEGIN
DECLARE @dt datetime
DECLARE @dts varchar(3)
DECLARE @t1 float
DECLARE @t2 float
DECLARE @r float
-- Obtain the time of latest executed statement
SET @dt = (
SELECT MAX(last_batch)
FROM master.dbo.sysprocesses
)
-- Select only the milliseconds
SET @dts = RIGHT(CONVERT(varchar(20), @dt, 114) , 3)
-- Scramble the digits
SET @t1 = CAST(SUBSTRING(@dts, 2, 1)
+ RIGHT(@dts, 1)
+ LEFT(@dts, 1) AS int)
-- Obtain the time of latest executed statement
SET @dt = (
SELECT MAX(last_batch)
FROM master.dbo.sysprocesses
)
-- Select only the milliseconds
SET @dts = RIGHT(CONVERT(varchar(20), @dt, 114) , 3)
-- Scramble the digits
SET @t2 = CAST(SUBSTRING(@dts, 2, 1)
+ RIGHT(@dts, 1)
+ LEFT(@dts, 1) AS int)
-- Select the random number
SET @r = '0'+ LEFT(RIGHT(CONVERT(varchar(40), @t1 * @t2 / pi(), 2), 21), 16)
-- Return the random number
RETURN @r
END
GO
You can identify several parts in the CREATE FUNCTION syntax for a scalar user-defined function:
CREATE FUNCTION ownername functionname, where you can specify the owner of the function and the name of the function.
() is an empty list of parameters. We will discuss parameters in the next section of this chapter.
RETURNS datatype, where you define the data type for the returned value as the function's result.
AS BEGIN...END to mark the function definition body.
The function definition body.
You can define the body of the function in a way similar to a stored procedure, declaring and using variables, using control-of-flow statements, accessing data from other tables, and other databases and servers.
Caution
Remember that you cannot modify data in existing tables inside a user-defined function directly. This includes the creation of temporary tables.
Because writing long user-defined functions can be complex, you can break down long functions into smaller ones that can be reused more often. Listing 10.5 creates a new version of the PRand function, created in Listing 10.4. This version uses a base function, called Get3Rand, to generate the scrambled three-digit number. The NewPRand function uses the Get3Rand function to generate two values and combine them to provide the new random number.
Code Listing 10.5. New Definition for the Random Function Using Other Base Functions
|
USE Northwind
GO
-- Create a base function to extract a three-digits
-- number based on the scrambled version of the
-- milliseconds information of the latest executed
-- statement in SQL Server
CREATE FUNCTION dbo.Get3Rand
()
RETURNS int
AS
BEGIN
DECLARE @dt datetime
DECLARE @dts varchar(3)
SET @dt = (
SELECT MAX(last_batch)
FROM master.dbo.sysprocesses
)
SET @dts = RIGHT(CONVERT(varchar(20), @dt, 114) , 3)
RETURN CAST(SUBSTRING(@dts, 2, 1)
+ RIGHT(@dts, 1)
+ LEFT(@dts, 1) AS int)
END
GO
-- Create the new NewPRand Random function
-- based on the Get3Rand function
CREATE FUNCTION dbo.NewPRand
()
RETURNS float
AS
BEGIN
DECLARE @r float
SET @r = '0'+ LEFT(RIGHT(CONVERT(varchar(40),
dbo.Get3Rand() * dbo.Get3Rand() / pi(), 2), 21), 16)
RETURN (@r)
END
Tip
If your user-defined function requires using temporary tables, you can use table variables instead. Table variables are defined inside the user-defined function and can be modified inside it.
Listing 10.6 uses table variables defined internally inside a user-defined function to store intermediate results. This function calculates the medium UnitPrice for products stored in the Products table.
Note
The medium value is the central value of an ordered list of values. The medium does not have to be equal to the average value.
Code Listing 10.6. Using Table Variables Inside a Scalar User-Defined Function
|
USE Northwind
GO
CREATE FUNCTION dbo.MediumProductUnitPrice
()
RETURNS money
AS
BEGIN
-- Create hosting table variable
DECLARE @t TABLE(
id int identity(1,1),
UnitPrice money)
-- Inserts the product prices in ascending order
INSERT INTO @t (UnitPrice)
SELECT UnitPrice
FROM Products
ORDER BY UnitPrice ASC
-- Selects the medium price
RETURN (
SELECT MAX(UnitPrice)
FROM @t
WHERE ID <=
(SELECT MAX(ID)
FROM @t) / 2
)
END
Using Parameters in User-Defined Functions
As you learned in Chapter 8, you can expand stored procedure capabilities by using parameters. You can create user-defined functions with parameters, too.
The examples from the preceding section do not use any parameter, which is why their execution does not depend on any value that the user might send. Most of the system-supplied mathematical functions accept one or more parameters and return a scalar result according to the mathematical operation to execute. Trigonometric functions use a number as a parameter and return a number as a result. String functions take one or more parameters and return a string.
You can create user-defined functions to expand the collection of system-supplied functions, using parameters. You must define a parameter list in the CREATE FUNCTION statement, after the function name. The parameters list is enclosed in parentheses. You must provide a data type for every parameter and, optionally, a default value.
Caution
The parameter list can be empty if the function does not use any input parameters. In this case, you must supply two parentheses () to specify an empty parameters list.
If you do not specify a parentheses-enclosed parameter list, or at least an empty one, you will get a syntax error when trying to create the user-defined function.
Listing 10.7 shows some examples of user-defined functions using parameters.
The first function, TotalPrice, computes the total price of a specific sale. You must provide the quantity sold, the unit price to apply, the agreed discount, and then the function returns the total price of the sale.
The second function in Listing 10.7, fn_FV, computes the future value of an annuity, the FV financial formula, as described in Microsoft Excel and Microsoft Visual Basic.
The third and fourth functions provide an example of how to create a user-defined function to perform basic encryption.
Caution
The intention of the SimpleEncrypt and SimpleDecrypt user-defined functions is only to show how to define a function to modify a string. The encryption used in these functions is too simple to be used in a production environment.
Code Listing 10.7. Some Scalar User-Defined Functions Using Parameters
|
USE Northwind
GO
-----------------------------------------------------------
-- Generic function to compute the total price of a sale
-- from the quantity, unitprice and discount
-----------------------------------------------------------
CREATE FUNCTION dbo.TotalPrice
(@Quantity float, @UnitPrice money, @Discount float = 0.0)
RETURNS money
AS
BEGIN
RETURN (@Quantity * @UnitPrice * (1.0 - @Discount))
END
GO
-----------------------------------------------------------
-- Compute the future value of an annuity based on
-- periodic fixed payments with a fixed interest rate
-- Parameters:
-- @rate: interest rate between payments
-- @nper: number of payments
-- @pmt: payment to be made on every period
-- @pv: present value. Default to 0.0
-- @type: 0 if the payment is made at the end of each period (default)
-- 1 if the payment is made at the beginning of each period
-----------------------------------------------------------
CREATE FUNCTION dbo.fn_FV
(@rate float, @nper int, @pmt money, @pv money = 0.0, @type bit = 0)
RETURNS money
AS
BEGIN
DECLARE @fv money
IF @rate = 0
SET @fv = @pv + @pmt * @nper
ELSE
SET @fv = @pv * POWER(1 + @rate, @nper) +
@pmt * (((POWER(1 + @rate, @nper + @type) - 1) / @rate) - @type)
RETURN (-@fv)
END
GO
-----------------------------------------------------------
-- Encrypt the string increasing the Unicode value of every
-- character by the number of characters in the string
-----------------------------------------------------------
CREATE Function dbo.SimpleEncrypt
(@string nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @output nvarchar(4000)
DECLARE @i int, @l int, @c int
SET @i = 1
SET @l = len(@string)
SET @output = ''
WHILE @i <= @l
BEGIN
SET @c = UNICODE(SUBSTRING(@string, @i, 1))
SET @output = @output +
CASE
WHEN @c > 65535 - @l
THEN NCHAR(@c + @l - 65536)
ELSE NCHAR(@c + @l) END
SET @i = @i + 1
END
RETURN @output
END
GO
-----------------------------------------------------------
-- Decrypt the string decreasing the Unicode value of every
-- character by the number of characters in the string
-----------------------------------------------------------
CREATE Function dbo.SimpleDecrypt
(@string nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @output nvarchar(4000)
DECLARE @i int, @l int, @c int
SET @i = 1
SET @l = len(@string)
SET @output = ''
WHILE @i <= @l
BEGIN
SET @c = UNICODE(SUBSTRING(@string, @i, 1))
SET @output = @output +
CASE
WHEN @c - @l >= 0
THEN NCHAR(@c - @l)
ELSE NCHAR(@c + 65535 - @l) END
SET @i = @i + 1
END
RETURN @output
END
GO
Listing 10.8 shows a more complex example. In this case, we want to create functions to convert angles from and to HMS format (hours, minutes, and seconds, or degrees, minutes, and seconds). We want to be able to use angles either in sexagesimal (1 circle = 360°) or centesimal (1 circle = 400g) units.
Note
Some mathematical books and scientific calculators refer to centesimal degrees as grades.
First, create the ANG_HMS user-defined function, which converts an angle into HMS format. To know the angle's units, define the @d_g parameter to specify the character that identifies the degrees unit. The @d_g parameter has only two possible values: 'g' (centesimal) or '°' (sexagesimal).
You can see in Listing 10.8 comments along the code of the ANG_HMS function, but in summary, it extracts from the angle in sequence: degrees, minutes, and seconds. The @precision parameter serves us to specify the number of digits to show for the fraction of the second.
Already having the generic ANG_HMS function, you can create the functions DEG_HMS and GRAD_HMS now to use directly in the ANG_HMS function.
Following a similar process, you can create the HMS_ANG, HMS_DEG, and HMS_GRAD functions.
Code Listing 10.8. Functions to Convert Angles to and from HMS Format
|
USE Northwind
GO
-----------------------------------------------------------
-- Generic function to convert an angle
-- into HMS format
-- @d_g indicates if the angle is measured in
-- sexagesimal degrees (CHAR(176) as the degrees symbol) or
-- centesimal degrees ('g', for grades)
-----------------------------------------------------------
CREATE FUNCTION dbo.ANG_HMS
(@angle float, @precision int = 0, @d_g CHAR(1))
RETURNS varchar(21)
AS
BEGIN
-- Declare variables
DECLARE @sign float
DECLARE @ncircles int
DECLARE @degrees int
DECLARE @minutes int
DECLARE @seconds float
DECLARE @secInt int
DECLARE @secDec int
DECLARE @frac float
DECLARE @hms varchar(20)
-- Save the sign of the angle
SET @sign = SIGN(@angle)
-- Take out the sign of the angle
-- to avoid calculation problems
SET @angle = ABS(@angle)
-- Extract the integer part as degrees
SET @degrees = CAST(FLOOR(@angle) AS int)
-- Count how many complete circles the angle has
SET @ncircles = @degrees /
CASE @d_g WHEN 'g'THEN 400
ELSE 360 END
-- Convert the angle into an angle from the first circle
SET @degrees = @degrees %
CASE @d_g WHEN 'g'THEN 400
ELSE 360 END
-- Extract the decimal part from the angle
SET @frac = @angle - @degrees - (@ncircles *
CASE @d_g WHEN 'g'THEN 400
ELSE 360 END
)
-- Extract minutes from the decimal part
SET @minutes = FLOOR(@frac *
CASE @d_g WHEN 'g'THEN 100
ELSE 60 END
)
-- Extract the number of seconds
SET @seconds = (@frac *
CASE @d_g WHEN 'g'THEN 100
ELSE 60 END
- @minutes) *
CASE @d_g WHEN 'g'THEN 100
ELSE 60 END
-- Calculate the number of complete seconds
SET @secInt = FLOOR(@seconds)
-- Set a limit for the fraction of a second to 9 digits
IF @precision > 9
SET @precision = 9
-- Extract the fraction of a second in the given precision
SET @secDec = (@seconds - @secInt) * POWER(10, @precision)
-- Start creating the resulting string with the sign
-- only for negative numbers
SET @hms = CASE @sign
WHEN -1 THEN '- '
ELSE ''END
-- Create the HMS format
SET @hms = @hms
+ CAST(@degrees AS varchar(3))
+ CHAR(176) + ''
+ CAST(@minutes AS varchar(2))
+ 'm '
+ CAST(@secInt AS varchar(2))
+ 's'
+ CAST(@secDec AS varchar(10))
RETURN @hms
END
GO
----------------------------------
-- Function to convert angles
-- measured in centesimal degrees
-- into HMS format
----------------------------------
CREATE FUNCTION dbo.GRAD_HMS
(@angle float, @precision int = 0)
RETURNS varchar(21)
AS
BEGIN
-- Call the ANG_HMS function with the 'g'format
RETURN dbo.ANG_HMS(@angle, @precision, 'g')
END
GO
-----------------------------------
-- Function to convert angles
-- measured in sexagesimal degrees
-- into HMS format
-----------------------------------
CREATE FUNCTION dbo.DEG_HMS
(@angle float, @precision int = 0)
RETURNS varchar(21)
AS
BEGIN
-- Call the ANG_HMS function with the CHAR(176) format
RETURN dbo.ANG_HMS(@angle, @precision, CHAR(176))
END
GO
-----------------------------------------------------------
-- Generic function to convert an angle
-- from HMS format
-- @d_g indicates if the angle is measured in
-- sexagesimal degrees (CHAR(176) as the degrees symbol) or
-- centesimal degrees ('g', for grades)
-----------------------------------------------------------
CREATE FUNCTION dbo.HMS_ANG
(@hms varchar(22), @d_g char(1))
RETURNS float
AS
BEGIN
-- Declare variables
DECLARE @sign float
DECLARE @pos0 int
DECLARE @posg int
DECLARE @posm int
DECLARE @poss int
DECLARE @degrees float
DECLARE @angle float
DECLARE @minutes float
DECLARE @seconds float
DECLARE @secInt float
DECLARE @secDec float
-- Extract the sign
IF RIGHT(@hms, 1) = '-'
SELECT @sign = -1, @pos0 = 2
ELSE
SELECT @sign = 1, @pos0 = 1
-- Search for the position in the string
-- of the character dividing degrees, minutes, and seconds
SET @posg = CHARINDEX(@d_g, @hms)
SET @posm = CHARINDEX('m', @hms)
SET @poss = CHARINDEX('s', @hms)
-- Extract the value of the degrees
SET @degrees = SUBSTRING(@hms, @pos0, @posg - @pos0)
-- Extract the value of the minutes
SET @minutes = SUBSTRING(@hms, @posg + 1, @posm - @posg - 1)
-- Extract the value of the seconds
-- as integer and decimal part
SET @secInt = SUBSTRING(@hms, @posm + 1, @poss - @posm - 1)
SET @secDec = SUBSTRING(@hms, @poss + 1, len(@hms) - @poss)
/ POWER(10.0, len(@hms) - @poss)
-- Calculate the angle
SET @angle = @sign * (
@degrees +
@minutes /
CASE @d_g WHEN 'g'THEN 100.0
ELSE 60.0 END +
(@secInt + @secdec) /
CASE @d_g WHEN 'g'THEN 10000.0
ELSE 3600.0 END)
-- Return the value
RETURN @angle
END
GO
----------------------------------
-- Function to convert angles
-- measured in sexagesimal degrees
-- from HMS format
----------------------------------
CREATE FUNCTION dbo.HMS_DEG
(@hms varchar(22))
RETURNS float
AS
BEGIN
-- Call the HMS_ANG function with the CHAR(176) format
RETURN dbo.HMS_ANG(@hms, CHAR(176))
END
GO
----------------------------------
-- Function to convert angles
-- measured in centesimal degrees
-- from HMS format
----------------------------------
CREATE FUNCTION dbo.HMS_GRAD
(@hms varchar(22))
RETURNS float
AS
BEGIN
-- Call the ANG_HMS function with the 'g'format
RETURN (dbo.HMS_ANG(@hms, 'g'))
END
GO
Invoking Scalar User-Defined Functions
You can use scalar user-defined functions anywhere in any Transact-SQL statement in which an expression is allowed.
If you want to invoke a user-defined function, you must qualify the function name with its owner, usually dbo. Listing 10.9 shows some statements using the MaxProductID in different valid ways.
Code Listing 10.9. How to Invoke a Scalar User-Defined Function
|
SELECT dbo.MaxProductID()
GO
SELECT ProductID, dbo.MaxProductID() AS 'MaxID'
FROM Products
GO
UPDATE [Order Details]
SET ProductID = dbo.MaxProductID()
WHERE ProductID = 25
GO
SELECT ProductID, MaxID
FROM Products
CROSS JOIN (SELECT dbo.MaxProductID() AS 'MaxID') AS MI
GO
SELECT P.ProductID, Quantity
FROM Products AS P
JOIN [Order Details] AS OD
ON P.ProductID = OD.ProductID
AND P.ProductID = dbo.MaxProductID()
GO
SELECT P.ProductID, Quantity
FROM Products AS P
JOIN [Order Details] AS OD
ON P.ProductID = OD.ProductID
WHERE P.ProductID = dbo.MaxProductID()
GO
SELECT P.ProductID, SUM(Quantity)
FROM Products AS P
JOIN [Order Details] AS OD
ON P.ProductID = OD.ProductID
GROUP BY P.ProductID
HAVING P.ProductID = dbo.MaxProductID()
GO
SELECT ProductID, ProductName,
CASE ProductID
WHEN dbo.MaxProductID() THEN 'Last Product'
ELSE ''END AS Note
FROM Products
GO
DECLARE @ID int
SET @ID = dbo.MaxProductID()
Caution
User-defined functions are local to the database where they are created. Although it is possible to create global user-defined functions, Microsoft does not support this functionality.
If a user-defined function uses parameters, you must specify a value for each parameter on every call, even if they have default values. You can use the keyword DEFAULT to provide the default value for a parameter that has a default value. You cannot omit a parameter when calling a user-defined function—because it has a default value already—otherwise, you will receive a syntax error message, as in Listing 10.10, where you can use the TotalPrice function without providing a value for discount, hoping that the function will use the default value.
Code Listing 10.10. Failing to Provide a Parameter Produces a Syntax Error
|
USE Northwind
GO
-- This is an illegal call, because it does not provide
-- a value for the @discount parameter
SELECT dbo.TotalPrice (12, 25.4)
GO
-- This is a valid call, because it does provide
-- a value for every parameter
SELECT dbo.TotalPrice (12, 25.4, 0.0)
GO
Server: Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo
.TotalPrice.
---------------------
304.8000
Listing 10.11 shows how to invoke some of the functions defined in Listings 10.4, 10.5, 10.6, 10.7, and 10.8. These examples show how to use these functions in different kinds of statements.
Code Listing 10.11. How to Invoke Scalar User-Defined Functions
|
USE Northwind
GO
-- dbo.MaxProductID
SELECT dbo.MaxProductID()
AS MaxProductID
GO
-- dbo.WhoWhere
SELECT dbo.WhoWhere() AS [Who from Where]
GO
-- Create a table using WHoWhere
-- As a DEFAULT constraint
IF OBJECT_ID('TestWhoWhere') IS NOT NULL
DROP TABLE TestWhoWhere
GO
CREATE TABLE TestWhoWhere(
ID int IDENTITY (1,1)
PRIMARY KEY,
Name nvarchar(40),
WhoWhere nvarchar(256) DEFAULT dbo.WhoWhere())
INSERT TestWhoWhere (Name)
VALUES ('New record')
SELECT *
FROM TestWhoWhere
GO
-- Create a trigger to Update automatically
-- Who did it and from where the change was done
CREATE TRIGGER trWhoWhere
ON TestWhoWhere
AFTER INSERT, UPDATE
AS
UPDATE TestWhoWhere
SET WhoWhere = dbo.WhoWhere()
FROM TestWhoWhere T
JOIN Inserted I
ON T.ID = I.ID
GO
INSERT TestWhoWhere
(Name, WhoWhere)
VALUES
('More records', 'nobody from nowhere')
SELECT *
FROM TestWhoWhere
-- dbo.Today
SELECT dbo.Today()
AS Today
-- Inserting an Order in the present month
INSERT Orders (CustomerID, OrderDate, ShippedDate)
VALUES ('WELLI', '2000-11-23', '2000-12-12')
-- Searching for Orders in the last 30 days
SELECT OrderID, CustomerID, OrderDate, ShippedDate
FROM Orders
WHERE DATEDIFF(day, ShippedDate, dbo.Today()) < 30
GO
-- dbo.PRand
SELECT dbo.PRand() AS PRand
GO
-- dbo.Get3Rand
SELECT dbo.Get3Rand() as Get3Rand1
GO
SELECT dbo.Get3Rand() as Get3Rand2
GO
-- dbo.NewPRand
SELECT dbo.NewPRand() as NewPRand
GO
-- dbo.MediumProductUnitPrice
SELECT dbo.MediumProductUnitPrice()
GO
-- Get the Medium 10 products
-- by UnitPrice
PRINT CHAR(10) + 'Medium 10 Products'+ CHAR(10)
SELECT *
FROM (SELECT TOP 6
UnitPrice, ProductID, ProductName
FROM Products
WHERE UnitPrice >= dbo.MediumProductUnitPrice()
ORDER BY UnitPrice ASC) AS A
UNION
SELECT *
FROM (SELECT TOP 5
UnitPrice, ProductID, ProductName
FROM Products
WHERE UnitPrice <= dbo.MediumProductUnitPrice()
ORDER BY UnitPrice DESC) AS B
ORDER BY UnitPrice ASC
-- dbo.fn_FV
SELECT 0.07 AS Rate,
36 AS NPer,
1000 AS Pmt,
10000 AS Pv,
0 AS Type,
dbo.fn_FV(0.07, 36, 1000, 10000, 0) AS FV
-- dbo.SimpleEncrypt
SELECT dbo.SimpleEncrypt('Hello World')
AS [Encrypted Message]
-- dbo.SimpleDecrypt
SELECT dbo.SimpleDecrypt('Hello World')
AS [Decrypted version of a non-encrypted message]
SELECT dbo.SimpleDecrypt(dbo.SimpleEncrypt('Hello World'))
AS [Decrypted version of an encrypted message]
-- dbo.ANG_HMS
SELECT dbo.ANG_HMS(12.3456, 3, 'g') AS 'GRAD to HMS Using ANG_HMS'
SELECT dbo.ANG_HMS(12.3456, 3, CHAR(176)) AS 'DEG to HMS Using ANG_HMS'
-- dbo.GRAD_HMS
SELECT dbo.GRAD_HMS(12.3456, DEFAULT) AS 'GRAD to HMS Using GRAD_HMS'
-- dbo.DEG_HMS
SELECT dbo.DEG_HMS(12.3456, 2) AS 'DEG to HMS Using DEG_HMS'
-- dbo.HMS_ANG
SELECT dbo.HMS_ANG('12g 34m 56s789', 'g') AS 'HMS to GRAD Using HMS_ANG'
SELECT dbo.HMS_ANG('12 34m 56s789', ' ') AS 'HMS to DEG Using HMS_ANG'
-- dbo.HMS_DEG
SELECT dbo.HMS_DEG('12 34m 56s789') AS 'HMS to DEG Using HMS_DEG'
-- dbo.HMS_GRAD
SELECT dbo.HMS_GRAD('12g 34m 56s789') AS 'HMS to GRAD Using HMS_GRAD'
SELECT dbo.GRAD_HMS(dbo.HMS_GRAD('12g 34m 56s789'), 3)
AS 'HMS to GRAD and to HMS again using HMS_GRAD and GRAD_HMS'
GO
MaxProductID
------------
77
Who from Where
-------------------------------------------
SQLBYEXAMPLESQLAdmin FROM SQL Query Analyzer
ID Name WhoWhere
-------- --------------- ---------------------------------------------
1 New record SQLBYEXAMPLESQLAdmin FROM SQL Query Analyzer
ID Name WhoWhere
-------- --------------- ---------------------------------------------
1 New record SQLBYEXAMPLESQLAdmin FROM SQL Query Analyzer
2 More records SQLBYEXAMPLESQLAdmin FROM SQL Query Analyzer
Today
------------------------------------------------------
2000-12-26 00:00:00
OrderID CustomerID OrderDate ShippedDate
----------- ---------- ------------------------ -----------------------
11109 WELLI 2000-11-23 00:00:00.000 2000-12-12 00:00:00.000
11110 WELLI 2000-11-23 00:00:00.000 2000-12-12 00:00:00.000
11111 WELLI 2000-11-23 00:00:00.000 2000-12-12 00:00:00.000
PRand
-----------------------------------------------------
0.40721851426491701
Get3Rand1
-----------
475
Get3Rand2
-----------
575
NewPRand
-----------------------------------------------------
5.2412061195157997E-2
---------------------
19.4500
Medium 10 Products
UnitPrice ProductID ProductName
--------------------- ----------- ----------------------------------------
18.0000 1 Chai
18.4000 40 Boston Crab Meat
19.0000 2 Chang
19.0000 36 Inlagd Sill
19.4500 44 Gula Malacca
19.5000 57 Ravioli Angelo
20.0000 49 Maxilaku
21.0000 11 Queso Cabrales
21.0000 22 Gustaf's Knäckebröd
21.0500 65 Louisiana Fiery Hot Pepper Sauce
Rate NPer Pmt Pv Type FV
---- ----------- ----------- ----------- ----------- ---------------------
.07 36 1000 10000 0 -263152.8817
Encrypted Message
---------------------------
Spwwz+bz} wo
Decrypted version of a non-encrypted message
---------------------------------------------
=Zaad_LdgaY
Decrypted version of an encrypted message
---------------------------------------------
Hello World
GRAD to HMS Using ANG_HMS
-------------------------
12 34m 55s999
DEG to HMS Using ANG_HMS
------------------------
12 20m 44s159
GRAD to HMS Using GRAD_HMS
--------------------------
12 34m 55s0
DEG to HMS Using DEG_HMS
------------`------------
12 20m 44s15
HMS to GRAD Using HMS_ANG
-----------------------------------------------------
12.345678899999999
HMS to DEG Using HMS_ANG
-----------------------------------------------------
12.582441388888888
HMS to DEG Using HMS_DEG
-----------------------------------------------------
12.582441388888888
HMS to GRAD Using HMS_GRAD
-----------------------------------------------------
12.345678899999999
HMS to GRAD and to HMS again using HMS_GRAD and GRAD_HMS
--------------------------------------------------------
12 34m 56s788
You can use fields as parameters when calling a user-defined function in any statement, such as in Listing 10.12.
Code Listing 10.12. You Can Apply Scalar User-Defined Functions to Table Fields
|
USE Northwind
GO
-- Use the TotalPrice function to retrieve
-- information from the Order Details table
SELECT OrderID, ProductID,
dbo.TotalPrice(Quantity, UnitPrice, Discount) AS TotalPrice
FROM [Order Details]
WHERE ProductID = 12
-- Use the SimpleEncrypt function to encrypt product names
SELECT ProductID,
dbo.SimpleEncrypt(ProductName) AS EncryptedName
FROM Products
WHERE CategoryID = 3
-- Use the SimpleDecrypt function to decrypt
-- a field encrypted with the SimpleEncrypt function
SELECT ProductID,
dbo.SimpleDecrypt(EncryptedName) AS ProductName
FROM (
SELECT ProductID, CategoryID,
dbo.SimpleEncrypt(ProductName) AS EncryptedName
FROM Products
) AS P
WHERE CategoryID = 3
OrderID ProductID TotalPrice
----------- ----------- ---------------------
10266 12 346.5600
10439 12 456.0000
10536 12 427.5000
10543 12 969.0000
10633 12 1162.8000
10678 12 3800.0000
10695 12 152.0000
10718 12 1368.0000
10968 12 1140.0000
10979 12 760.0000
11018 12 760.0000
11046 12 722.0000
11049 12 121.6000
11077 12 72.2000
ProductID ProductName
----------- -----------------------------
11 Queso Cabrales
12 Queso Manchego La Pastora
31 Gorgonzola Telino
32 Mascarpone Fabioli
33 Geitost
59 Raclette Courdavault
60 Camembert Pierrot
69 Gudbrandsdalsost
71 Flotemysost
72 Mozzarella di Giovanni
A scalar user-defined function is a special form of stored procedure, and you can execute scalar user-defined functions in the same way you execute stored procedures. In this case, you can omit parameters that have a default value and alter the order of the parameters, providing the parameter name in the user-defined function call in the same way you do for stored procedures. Listing 10.13 shows some examples of how to call a scalar user-defined function using the EXECUTE statement.
Code Listing 10.13. You Can Use EXECUTE to Invoke Scalar User-Defined Functions
|
USE Northwind
GO
--Declare a variable to receive the result of the UDF
DECLARE @Total money
-- Use EXECUTE and provide values for every parameter
EXECUTE @Total = dbo.TotalPrice 12, 25.4, 0.0
SELECT @Total
-- Use EXECUTE and omit the @Discount parameter
-- because it has a default value
EXECUTE @Total = dbo.TotalPrice 12, 25.4
SELECT @Total
-- Use EXECUTE and omit the UDF owner, because it defaults to dbo
EXECUTE @Total = TotalPrice 12, 25.4
SELECT @Total
-- Use EXECUTE and provide values for every parameter
-- specifying parameter names
EXECUTE @Total = TotalPrice
@Quantity = 12,
@UnitPrice = 25.4,
@Discount = 0.2
SELECT @Total
-- Use EXECUTE and provide values for every parameter
-- specifying parameter by order and by name on any order
EXECUTE @Total = TotalPrice 12,
@Discount = 0.2,
@UnitPrice = 25.4
SELECT @Total
---------------------
304.8000
---------------------
304.8000
---------------------
304.8000
---------------------
243.8400
---------------------
243.8400
Tip
If you invoke a user-defined function with the EXECUTE statement, you do not have to qualify the function with the owner name. However, it is always more efficient to qualify the objects you use, because in this way SQL Server does not have to search first for an object owned by you, before searching for the same object owned by dbo.
Inline Table-Valued User-Defined Functions
You can use views in any DML statement as if they were tables. You can use a WHERE clause inside the view definition to limit the view results to specific rows, but this restriction is fixed, because it is part of the view definition. In the query where you use the view, you can use another WHERE clause to limit the search. In this case, SQL Server combines both WHERE clauses in the final query plan, after the view definition is merged with the outer query.
SQL Server 2000 gives you a new feature to create something similar to parameterized views: inline user-defined functions.
An inline user-defined function contains a single SELECT statement but, unlike views, it can use several parameters to restrict the query, providing an easier call interface than views.
Tip
You can identify inline user-defined functions because they return a table and their definition has only a single SELECT statement, without a BEGIN...END block.
You can use an inline user-defined function wherever a table or view is accepted:
In the SELECT clause of a SELECT statement, as part of a subquery that returns a single value (a single row and single column result set).
In the SET clause of an UPDATE statement, as part of a subquery that provides a single value for a field in the table to be updated.
In the FROM clause of any DML statement.
In the WHERE or HAVING clauses of any DML statement, as part of a subquery that returns a single value to be compared to any field or variable.
In the WHERE clause of any DML statement, as part of a subquery introduced by EXISTS or NOT EXISTS.
In the WHERE or HAVING clause of any DML statement, as part of a subquery used with the IN or NOT IN operators, as long as the subquery returns a single column.
Creating Inline Table-Valued User-Defined Functions
To create an inline user-defined function, you must use the CREATE FUNCTION statement, as shown in Listing 10.14.
The first example creates the GetCustomersFromCountry function, which selects customers based in a specific country. This function returns a result set with the same structure as the original Customers table.
Based on the GetCustomersFromCountry function, you can create the GetCustomersFromUSA function, which retrieves customers based in the USA only.
The third example returns orders from a specific day using the GetOrdersFromDay function.
The fourth example creates the GetOrdersFromToday function to retrieve the list of today's orders. Note that you cannot base this function in GetOrdersFromDay function, because you cannot invoke an inline user-defined function using a scalar function as a parameter value.
Note
Inside the GetOrdersFromToday function you use the Today() scalar user-defined function, because Getdate() is not valid inside the definition of a user-defined function.
Later in this chapter, in the "Deterministic and Nondeterministic Functions" section, you will see the lists of functions that are not valid inside a user-defined function.
The next function is the OrdersWithValue function, which uses the TotalPrice scalar function to calculate the total value for every order. This inline user-defined function retrieves a result set with the same structure as the Orders table, plus the TotalValue field.
Based on the OrdersWithValue function, you can create the next two functions. OrdersByValue retrieves the orders where TotalValue is greater than a specific target total value. TopTenOrders returns the top 10 orders by TotalValue.
Code Listing 10.14. Inline User-Defined Functions Examples Using the CREATE FUNCTION Statement
|
USE Northwind
GO
-- Returns customers from a specific country
CREATE FUNCTION dbo.GetCustomersFromCountry
(@country nvarchar(15))
RETURNS TABLE
AS
RETURN (
SELECT *
FROM Customers
WHERE Country = @country
)
GO
-- Returns USA-based customers
CREATE FUNCTION dbo.GetCustomersFromUSA
()
RETURNS TABLE
AS
RETURN (
SELECT *
FROM dbo.GetCustomersFromCountry(N'USA')
)
GO
-- Returns the orders from a specific day
CREATE FUNCTION dbo.GetOrdersFromDay
(@date as smalldatetime)
RETURNS TABLE
AS
RETURN (
SELECT *
FROM Orders
WHERE DATEDIFF(day, OrderDate, @date) = 0
)
GO
-- Returns orders from today
CREATE FUNCTION dbo.GetOrdersFromToday
()
RETURNS TABLE
AS
RETURN (
SELECT *
FROM Orders
WHERE DATEDIFF(day, OrderDate, dbo.Today()) = 0
)
GO
-- Returns Orders with the total order value
CREATE FUNCTION dbo.OrdersWithValue
()
RETURNS TABLE
AS
RETURN (
SELECT O.*, TotalValue
FROM Orders O
JOIN (
SELECT OrderID, SUM(dbo.TotalPrice(Quantity, UnitPrice, Discount))
AS TotalValue
FROM [Order Details]
GROUP BY OrderID) AS OD
ON O.OrderID = OD.OrderID
)
GO
-- Returns orders with a value greater than
-- a specific target value
CREATE FUNCTION dbo.OrdersByValue
(@total money)
RETURNS TABLE
AS
RETURN (
SELECT *
FROM dbo.OrdersWithValue()
WHERE TotalValue > @total
)
GO
-- Returns the top 10 orders by total value
CREATE FUNCTION dbo.TopTenOrders
()
RETURNS TABLE
AS
RETURN (
SELECT TOP 10 WITH TIES *
FROM dbo.OrdersWithValue()
ORDER BY TotalValue DESC
)
GO
You can identify several parts of the CREATE FUNCTION syntax for inline user-defined function:
CREATE FUNCTION ownername.functionname, where you can specify the owner of the function and the name of the function.
(parameter_lists), where every parameter is identified by its name and the data type. The parameter list can be empty.
RETURNS TABLE, because inline user-defined functions always return a tablelike result set.
AS RETURN () marks the function definition body, which has to be enclosed inside the parenthesis block.
The SELECT statement, which defines the result set to return.
In essence, the creation of an inline user-define function is not very different from the creation of a view, except for the parameters list, which gives inline user-defined functions extra functionality not available in views.
Tip
Inline user-defined functions have the same restrictions that apply to views; look at Chapter 3, "Working with Tables and Views," for more information about them. One of these restrictions is that you cannot use ORDER BY, but you can use TOP 100 PERCENT...ORDER BY to produce the same result. Listing 10.15 shows how to implement this trick.
Code Listing 10.15. Use TOP 100 PERCENT to Create an Inline User-Defined Function That Produces Sorted Data
|
USE Northwind
GO
-- Returns Products ordered by ProductName
CREATE FUNCTION dbo.OrderedProducts()
RETURNS TABLE
AS
RETURN (SELECT TOP 100 PERCENT *
FROM Products
ORDER BY ProductName ASC)
GO
-- Test the function
SELECT TOP 10 ProductID, ProductName
FROM dbo.OrderedProducts()
ProductID ProductName
----------- ----------------------------------------
17 Alice Mutton
3 Aniseed Syrup
40 Boston Crab Meat
60 Camembert Pierrot
18 Carnarvon Tigers
1 Chai
2 Chang
39 Chartreuse verte
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
Invoking Data from Inline User-Defined Functions
You can invoke an inline user-defined function in the same way you invoke a table or a view in a DML statement, with the only exception that you must use parentheses after the function name, even if there are not any parameters to use.
SQL Server merges the definition of the Inline function with the definition of the query where the function is invoked, to create a unique query plan. This is the same way that SQL Server uses views to execute Transact-SQL statements.
Listing 10.16 shows how to invoke the inline user-defined functions defined in Listing 10.14 in different ways.
Code Listing 10.16. Invoking Inline User-Defined Functions
|
USE Northwind
GO
PRINT CHAR(10) + 'Use GetCustomersFromCountry(''Mexico'')'+ CHAR(10)
SELECT CustomerID, CompanyName, City
FROM dbo.GetCustomersFromCountry('Mexico')
PRINT CHAR(10) + 'Use GetCustomersFromUSA()'+ CHAR(10)
Select CustomerID, CompanyName, City
FROM dbo.GetCustomersFromUSA()
PRINT CHAR(10)
+ 'Use GetCustomersFromCountry(''Mexico'') with the IN operator'
+ CHAR(10)
SELECT OrderID,
CONVERT(varchar(10), OrderDate, 120) AS OrderDate
FROM Orders
WHERE CustomerID IN
(SELECT CustomerID
FROM dbo.GetCustomersFromCountry('Mexico'))
PRINT CHAR(10)
+ 'Joins OrdersByValue to Customers'
+ CHAR(10)
SELECT CompanyName, OrderID, TotalValue,
CONVERT(varchar(10), OrderDate, 120) AS OrderDate
FROM dbo.OrdersByValue(10000) AS OBV
JOIN Customers C
ON OBV.CustomerID = C.CustomerID
PRINT CHAR(10)
+ 'Joins TopTenOrders to Customers'
+ CHAR(10)
SELECT CompanyName, OrderID, TotalValue,
CONVERT(varchar(10), OrderDate, 120) AS OrderDate
FROM dbo.TopTenOrders() AS OBV
JOIN Customers C
ON OBV.CustomerID = C.CustomerID
Use GetCustomersFromCountry('Mexico')
CustomerID CompanyName City
---------- ---------------------------------------- ---------------
ANATR Ana Trujillo Emparedados y helados México D.F.
ANTON Antonio Moreno Taquería México D.F.
CENTC Centro comercial Moctezuma México D.F.
PERIC Pericles Comidas clásicas México D.F.
TORTU Tortuga Restaurante México D.F.
Use GetCustomersFromUSA()
CustomerID CompanyName City
---------- ---------------------------------------- ---------------
GREAL Great Lakes Food Market Eugene
HUNGC Hungry Coyote Import Store Elgin
LAZYK Lazy K Kountry Store Walla Walla
LETSS Let's Stop N Shop San Francisco
LONEP Lonesome Pine Restaurant Portland
OLDWO Old World Delicatessen Anchorage
RATTC Rattlesnake Canyon Grocery Albuquerque
SAVEA Save-a-lot Markets Boise
SPLIR Split Rail Beer & Ale Lander
THEBI The Big Cheese Portland
THECR The Cracker Box Butte
TRAIH Trail's Head Gourmet Provisioners Kirkland
WHITC White Clover Markets Seattle
Use GetCustomersFromCountry('Mexico') with the IN operator
OrderID OrderDate
----------- ----------
10259 1996-07-18
10276 1996-08-08
10293 1996-08-29
10304 1996-09-12
10308 1996-09-18
10319 1996-10-02
10322 1996-10-04
10354 1996-11-14
10365 1996-11-27
10474 1997-03-13
10502 1997-04-10
10507 1997-04-15
10518 1997-04-25
10535 1997-05-13
10573 1997-06-19
10576 1997-06-23
10625 1997-08-08
10676 1997-09-22
10677 1997-09-22
10682 1997-09-25
10759 1997-11-28
10842 1998-01-20
10856 1998-01-28
10915 1998-02-27
10926 1998-03-04
10995 1998-04-02
11069 1998-05-04
11073 1998-05-05
Joins OrdersByValue to Customers
CompanyName OrderID TotalValue OrderDate
---------------------------------- ----------- ------------- ----------
Simons bistro 10417 11188.4000 1997-01-16
Rattlesnake Canyon Grocery 10479 10495.6000 1997-03-19
QUICK-Stop 10540 10191.7000 1997-05-19
QUICK-Stop 10691 10164.8000 1997-10-03
Königlich Essen 10817 10952.8450 1998-01-06
QUICK-Stop 10865 16387.5000 1998-02-02
Rattlesnake Canyon Grocery 10889 11380.0000 1998-02-16
Hungry Owl All-Night Grocers 10897 10835.2400 1998-02-19
Hanari Carnes 10981 15810.0000 1998-03-27
Save-a-lot Markets 11030 12615.0500 1998-04-17
Joins TopTenOrders to Customers
CompanyName OrderID TotalValue OrderDate
---------------------------------- ----------- ------------- ----------
QUICK-Stop 10865 16387.5000 1998-02-02
Hanari Carnes 10981 15810.0000 1998-03-27
Save-a-lot Markets 11030 12615.0500 1998-04-17
Rattlesnake Canyon Grocery 10889 11380.0000 1998-02-16
Simons bistro 10417 11188.4000 1997-01-16
Königlich Essen 10817 10952.8450 1998-01-06
Hungry Owl All-Night Grocers 10897 10835.2400 1998-02-19
Rattlesnake Canyon Grocery 10479 10495.6000 1997-03-19
QUICK-Stop 10540 10191.7000 1997-05-19
QUICK-Stop 10691 10164.8000 1997-10-03
You can update, insert, or delete rows in tables through inline user-defined functions with the same limits as updating, inserting, or deleting rows in tables through views. Listing 10.17 shows an example of how to insert, update, and delete rows in the Orders table through the GetOrdersWithValue inline user-defined function.
Code Listing 10.17. Modify Data Through Inline User-Defined Functions
|
USE Northwind
GO
PRINT CHAR(10) + 'Before the Insert'+ CHAR(10)
SELECT CustomerID, CompanyName, Country
FROM dbo.GetCustomersFromUsa()
WHERE CustomerID > 'W'
INSERT dbo.GetCustomersFromUSA() (CustomerID, CompanyName, Country)
VALUES ('ZZZZZ', 'Dummy Customer', 'USA')
PRINT CHAR(10) + 'After the Insert'+ CHAR(10)
SELECT CustomerID, CompanyName, Country
FROM dbo.GetCustomersFromUsa()
WHERE CustomerID > 'W'
UPDATE dbo.GetCustomersFromUSA()
SET CompanyName = 'New Customer'
WHERE CustomerID = 'ZZZZZ'
PRINT CHAR(10) + 'After the Update'+ CHAR(10)
SELECT CustomerID, CompanyName, Country
FROM dbo.GetCustomersFromUsa()
WHERE CustomerID > 'W'
DELETE dbo.GetCustomersFromUSA()
WHERE CustomerID = 'ZZZZZ'
PRINT CHAR(10) + 'After the Delete'+ CHAR(10)
SELECT CustomerID, CompanyName, Country
FROM dbo.GetCustomersFromUsa()
WHERE CustomerID > 'W'
GO
DECLARE @ID int
PRINT CHAR(10) + 'Before the Insert'+ CHAR(10)
SELECT CustomerID, OrderID, TotalValue, OrderDate
FROM dbo.OrdersWithValue()
WHERE CustomerID = 'VINET'
INSERT dbo.OrdersWithValue() (CustomerID, OrderDate)
SELECT 'VINET', dbo.Today()
-- Retrieve the latest Identity value in this session
SET @ID = SCOPE_IDENTITY()
INSERT [Order Details]
(OrderID, ProductID, Quantity, UnitPrice, Discount)
SELECT @ID, 28, 10, UnitPrice, 0.1
FROM Products
WHERE ProductID = 28
PRINT CHAR(10) + 'After the Insert'+ CHAR(10)
SELECT CustomerID, OrderID, TotalValue, OrderDate
FROM dbo.OrdersWithValue()
WHERE CustomerID = 'VINET'
DELETE [Order Details]
WHERE OrderID = @ID
DELETE Orders
WHERE OrderID = @ID
PRINT CHAR(10) + 'After the Delete'+ CHAR(10)
SELECT CustomerID, OrderID, TotalValue, OrderDate
FROM dbo.OrdersWithValue()
WHERE CustomerID = 'VINET'
Before the Insert
CustomerID CompanyName Country
---------- ---------------------------------------- ---------------
WHITC White Clover Markets USA
After the Insert
CustomerID CompanyName Country
---------- ---------------------------------------- ---------------
WHITC White Clover Markets USA
ZZZZZ Dummy Customer USA
After the Update
CustomerID CompanyName Country
---------- ---------------------------------------- ---------------
WHITC White Clover Markets USA
ZZZZZ New Customer USA
After the Delete
CustomerID CompanyName Country
---------- ---------------------------------------- ---------------
WHITC White Clover Markets USA
Before the Insert
CustomerID OrderID TotalValue OrderDate
---------- ----------- --------------------- -----------------------
VINET 10248 440.0000 1996-07-04 00:00:00.000
VINET 10274 538.6000 1996-08-06 00:00:00.000
VINET 10295 121.6000 1996-09-02 00:00:00.000
VINET 10737 139.8000 1997-11-11 00:00:00.000
VINET 10739 240.0000 1997-11-12 00:00:00.000
After the Insert
CustomerID OrderID TotalValue OrderDate
---------- ----------- --------------------- -----------------------
VINET 10248 440.0000 1996-07-04 00:00:00.000
VINET 10274 538.6000 1996-08-06 00:00:00.000
VINET 10295 121.6000 1996-09-02 00:00:00.000
VINET 10737 139.8000 1997-11-11 00:00:00.000
VINET 10739 240.0000 1997-11-12 00:00:00.000
VINET 11118 410.4000 2000-12-26 00:00:00.000
After the Delete
CustomerID OrderID TotalValue OrderDate
---------- ----------- --------------------- -----------------------
VINET 10248 440.0000 1996-07-04 00:00:00.000
VINET 10274 538.6000 1996-08-06 00:00:00.000
VINET 10295 121.6000 1996-09-02 00:00:00.000
VINET 10737 139.8000 1997-11-11 00:00:00.000
VINET 10739 240.0000 1997-11-12 00:00:00.000
Note
You cannot delete data from the OrdersWithValue function, because it joins multiple tables.
Table-Valued Functions
Multistatement table-valued user-defined functions (or table-valued functions) are similar to inline user-defined functions. Actually, you can use them in the same scenarios, but you are not restricted to defining them as a single SELECT statement.
A table-valued function returns a single result set—the contents of a table variable with a predefined format specified in the function declaration.
Caution
The result of a table-valued function is always read-only. The result of an inline user-defined function can be read-only or not, depending on the SELECT statement used to define the function.
As mentioned in the preceding section, inline user-defined functions are similar to views in both structure and use. Table-valued functions are similar to stored procedures, but they return a single result set.
You can define parameters in a table-valued function, as you did with scalar user-defined functions and stored procedures. However, you cannot create an OUTPUT parameter in a user-defined function.
Creating Table-Valued Functions
To create a table-valued function, you must use the CREATE FUNCTION statement. Listing 10.18 shows functions similar to those in Listing 10.14, but in table-valued versions.
Code Listing 10.18. Table-Valued Version of the Inline User-Defined Functions Examples from Listing 10.14
|
USE Northwind
GO
CREATE FUNCTION dbo.tv_GetCustomersFromCountry
(@country nvarchar(15))
RETURNS @List TABLE
(CustomerID nchar(5),
CompanyName nvarchar(40),
Country nvarchar(15))
AS
BEGIN
INSERT @List
SELECT CustomerID, CompanyName, Country
FROM Customers
WHERE Country = @country
RETURN
END
GO
-- Returns USA-based customers
CREATE FUNCTION dbo.tv_GetCustomersFromUSA
()
RETURNS @List TABLE
(CustomerID nchar(5),
CompanyName nvarchar(40),
Country nvarchar(15))
AS
BEGIN
INSERT @List
SELECT CustomerID, CompanyName, Country
FROM dbo.GetCustomersFromCountry(N'USA')
RETURN
END
GO
-- Returns the orders from a specific day
CREATE FUNCTION dbo.tv_GetOrdersFromDay
(@date as smalldatetime)
RETURNS @list TABLE
(OrderID int,
OrderDate datetime)
AS
BEGIN
INSERT @List
SELECT OrderID, OrderDate
FROM Orders
WHERE DATEDIFF(day, OrderDate, @date) = 0
RETURN
END
GO
-- Returns orders from today
CREATE FUNCTION dbo.tv_GetOrdersFromToday
()
RETURNS @list TABLE
(OrderID int,
OrderDate datetime)
AS
BEGIN
INSERT @List
SELECT OrderID, OrderDate
FROM Orders
WHERE DATEDIFF(day, OrderDate, dbo.Today()) = 0
RETURN
END
GO
-- Returns Orders with the total order value
CREATE FUNCTION dbo.tv_OrdersWithValue
()
RETURNS @list TABLE
(OrderID int,
CustomerID nchar(5),
OrderDate datetime,
TotalValue money)
AS
BEGIN
INSERT @List
SELECT O.OrderID, CustomerID, OrderDate, TotalValue
FROM Orders O
JOIN (
SELECT OrderID, SUM(dbo.TotalPrice(Quantity, UnitPrice, Discount))
AS TotalValue
FROM [Order Details]
GROUP BY OrderID) AS OD
ON O.OrderID = OD.OrderID
RETURN
END
GO
-- Returns orders with a value greater than
-- a specific target value
CREATE FUNCTION dbo.tv_OrdersByValue
(@total money)
RETURNS @list TABLE
(OrderID int,
CustomerID nchar(5),
OrderDate datetime,
TotalValue money)
AS
BEGIN
INSERT @List
SELECT OrderID, CustomerID, OrderDate, TotalValue
FROM dbo.OrdersWithValue()
WHERE TotalValue > @total
RETURN
END
GO
-- Returns the top 10 orders by total value
CREATE FUNCTION dbo.tv_TopTenOrders
()
RETURNS @list TABLE
(OrderID int,
CustomerID nchar(5),
OrderDate datetime,
TotalValue money)
AS
BEGIN
INSERT @List
SELECT TOP 10 WITH TIES
OrderID, CustomerID, OrderDate, TotalValue
FROM dbo.OrdersWithValue()
ORDER BY TotalValue DESC
RETURN
END
GO
You can identify several parts in the CREATE FUNCTION syntax for table- valued functions:
CREATE FUNCTION ownername.functionname, where you can specify the owner of the function and the name of the function.
(parameter_lists), where every parameter is identified by its name and the data type. The parameter list can be empty. Parameters can have default values.
RETURNS @tablename TABLE, because table-valued functions always return a tablelike result set, which is the contents of the table variable defined in this line as @tablename.
(Field definition, ...), definition of every field on the @tablename table variable, following the same rules as declaring fields in a standard table object.
AS BEGIN...END marks the function definition body, which must be written inside the BEGIN END block.
The RETURN statement, standalone, which sends the table variable back to the invoking process as a result set.
The contents of a table-valued function are similar to a stored procedure that uses a temporary table to store intermediate results and select from that temporary table at the end.
Tip
Inside scalar or table-valued user-defined functions you cannot create temporary tables; instead, you can use table variables. However, it is a good practice to replace temporary tables with table variables in stored procedures and scripts whenever this might be possible.
Looking at the examples in Listing 10.18, you could say that table-valued functions are similar to inline user-defined functions, except that you must declare the fields of the returning table explicitly. However, table-valued functions give you far more flexibility than inline user-defined functions, in a similar way as stored procedures give you more flexibility than views.
Listing 10.19 shows two more complex examples in which the creation of a table-valued function solves the problem.
The first function is called MakeList, and it converts a string with several items into a single column result set where every item is stored in a single row. You can specify any separator to divide values, or use the default '|'. This function can be useful in a WHERE clause introduced with the IN operator.
The second function is a bit more complex. It produces a subset of a fact table from the Order Details table, adding some extra information as the CategoryName, ProductName, CompanyName, and OrderDate from the Categories, Products, Customers, and Orders tables, and the TotalValue using the TotalPrice scalar user-defined function.
To call this function, you can specify whether you want a full list (@Key IS NULL), a list for a specific order (@Key = 'ORD' and @ID = OrderID), a specific Customer (@Key = 'CUS' and @ID = CustomerID), a product (@Key = 'PRO' and ID = ProductID), or a category (@Key = 'CAT' and @ID = CategoryID).
At the beginning of the function, you can find a sequence of IF structures to apply the most efficient method to every case to prepare the initial list of rows to return.
At the end of the OrderDetailsComplete function, fill the missing information with values coming from other tables.
Code Listing 10.19. Using Table-Valued Functions to Execute Complex Result Sets
|
USE Northwind
GO
-- Converts a string containing a list of items
-- into a single column table where every item
-- is in a separate row
-- using any character as a separator
CREATE FUNCTION dbo.MakeList
(@ParamArray as nvarchar(4000), @Separator as char(1) = '|')
RETURNS @List TABLE
(Item sql_variant)
AS
BEGIN
DECLARE @pos int, @pos0 int
SET @pos0 = 0
WHILE 1=1
BEGIN
SET @pos = CHARINDEX(@Separator, @ParamArray, @pos0 + 1)
INSERT @List
SELECT CASE @pos
WHEN 0 THEN SUBSTRING(@ParamArray, @pos0+1,
LEN(@ParamArray) - @pos -1)
ELSE SUBSTRING(@ParamArray, @pos0+1, @pos - @pos0-1)
END
IF @pos = 0 BREAK
SET @pos0 = @pos
END
RETURN
END
GO
-- Produces a list of orders
-- with full descriptive information
-- ProductName, CategoryName, CompanyName
-- OrderDate and TotalValue
-- with every primary key to link to other tables
-- The list can be produced for every
-- Order (@Key = 'ORD'),
-- Product (@Key = 'PRO'),
-- Customer (@Key = 'CUS'),
-- Category (@Key = 'CAT')
-- Full List (@Key NOT IN ('ORD', 'PRO', 'CUS', 'CAT'))
CREATE FUNCTION dbo.OrderDetailsComplete
(@ID sql_variant = NULL,
@Key char(3) = NULL)
RETURNS @Details TABLE
(OrderID int,
ProductID int,
CustomerID nchar(5) NULL,
CategoryID int NULL,
OrderDate smalldatetime NULL,
Value money NULL,
Category nvarchar(15) NULL,
Product nvarchar(40) NULL,
Company nvarchar(40) NULL)
AS
BEGIN
IF @Key = 'ORD'
BEGIN
INSERT @Details
(OrderID, ProductID, Value)
SELECT OrderID, ProductID,
dbo.TotalPrice(Quantity, UnitPrice, Discount)
FROM [Order Details]
WHERE OrderID = @ID
END
ELSE IF @Key = 'PRO'
BEGIN
INSERT @Details (OrderID, ProductID, Value)
SELECT OrderID, ProductID,
dbo.TotalPrice(Quantity, UnitPrice, Discount)
FROM [Order Details]
WHERE ProductID = @ID
END
ELSE IF @Key = 'CUS'
BEGIN
INSERT @Details (OrderID, ProductID, CustomerID, Value)
SELECT O.OrderID, ProductID, CustomerID,
dbo.TotalPrice(Quantity, UnitPrice, Discount)
FROM [Order Details] OD
JOIN Orders O
ON O.OrderID = OD.OrderID
WHERE CustomerID = @ID
END
ELSE IF @Key = 'CAT'
BEGIN
INSERT @Details (OrderID, ProductID, CategoryID, Value)
SELECT OD.OrderID, P.ProductID, CategoryID,
dbo.TotalPrice(Quantity, OD.UnitPrice, Discount)
FROM [Order Details] OD
JOIN Products P
ON P.ProductID = OD.ProductID
WHERE CategoryID = @ID
END
ELSE
BEGIN
INSERT @Details
(OrderID, ProductID, Value)
SELECT OrderID, ProductID,
dbo.TotalPrice(Quantity, UnitPrice, Discount)
FROM [Order Details]
END
UPDATE D
SET D.CustomerID = O.CustomerID,
D.OrderDate = O.OrderDate
FROM @Details D
JOIN Orders O
ON O.OrderID = D.OrderID
WHERE D.CustomerID IS NULL
UPDATE D
SET D.CategoryID = P.CategoryID,
D.Product = P.ProductName
FROM @Details D
JOIN Products P
ON P.ProductID = D.ProductID
WHERE D.CategoryID IS NULL
UPDATE D
SET D.Category = C.CategoryName
FROM @Details D
JOIN Categories C
ON C.CategoryID = D.CategoryID
UPDATE D
SET D.Company = C.CompanyName
FROM @Details D
JOIN Customers C
ON C.CustomerID = D.CustomerID
RETURN
END
GO
Invoking Table-Valued User-Defined Functions
You can invoke table-value functions the same way you do for inline user-defined functions. Listing 10.20 shows examples of how to call some functions defined in Listing 10.18.
Note
Listing 10.20 is a revised version of Listing 10.16, calling table-value functions instead of inline user-defined functions. The output is the same as in Listing 10.16; therefore, it is not necessary to show it again.
Code Listing 10.20. Calling Table-Value Functions
|
USE Northwind
GO
PRINT CHAR(10) + 'Use tv_GetCustomersFromCountry(''Mexico'')'+ CHAR(10)
SELECT CustomerID, CompanyName, Country
FROM dbo.tv_GetCustomersFromCountry('Mexico')
PRINT CHAR(10) + 'Use tv_GetCustomersFromUSA()'+ CHAR(10)
Select CustomerID, CompanyName, Country
FROM dbo.tv_GetCustomersFromUSA()
PRINT CHAR(10)
+ 'Use tv_GetCustomersFromCountry(''Mexico'') with the IN operator'
+ CHAR(10)
SELECT OrderID,
CONVERT(varchar(10), OrderDate, 120) AS OrderDate
FROM Orders
WHERE CustomerID IN
(SELECT CustomerID
FROM dbo.tv_GetCustomersFromCountry('Mexico'))
PRINT CHAR(10)
+ 'Joins tv_OrdersByValue to Customers'
+ CHAR(10)
SELECT CompanyName, OrderID, TotalValue,
CONVERT(varchar(10), OrderDate, 120) AS OrderDate
FROM dbo.tv_OrdersByValue(10000) AS OBV
JOIN Customers C
ON OBV.CustomerID = C.CustomerID
PRINT CHAR(10)
+ 'Joins tv_TopTenOrders to Customers'
+ CHAR(10)
SELECT CompanyName, OrderID, TotalValue,
CONVERT(varchar(10), OrderDate, 120) AS OrderDate
FROM dbo.tv_TopTenOrders() AS OBV
JOIN Customers C
ON OBV.CustomerID = C.CustomerID
However, the way SQL Server executes table-valued functions and inline user-defined functions is completely different:
The definition of inline user-defined functions is merged with the definition of the outer query, producing a single query plan, in which you cannot find any traces of the user-defined function. Figure 10.1 shows the query plan of this query, in which you can see an Index Scan on the Customers table's clustered index. Every time you use an inline user-defined function its definition must be merged again with the outer query, producing a new query plan.
Figure 10.1. Query plan of a query that uses an inline user-defined function.
The first time you call a table-valued function, it is compiled and a query plan is placed in memory. Every time you use this function, the calling query forces the execution of the saved query plan and the function returns the table variable, which will be used in the outer query. Figure 10.2 shows the query plan of this execution, simpler than the one from Figure 10.1, in which you can see a Table Scan on the table variable returned from the function. In this case, most of the work is done in a separate query plan, corresponding to the table- valued function.
Figure 10.2. Query plan of a query that uses a table-valued user-defined function.
