Dela via

  • Artikel

Executing User-Defined Functions (Database Engine)

User-defined functions can be invoked in queries or in other statements or expressions such as computed columns or string expressions. Scalar-valued functions can be executed using the EXECUTE statement.

Invoking User-Defined Functions That Return a Scalar Value

You can invoke a user-defined function that returns a scalar value anywhere that a scalar expression of the same data type is allowed in Transact-SQL statements. Scalar-valued functions must be invoked by using at least the two-part name of the function. For more information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). 

Queries

User-defined functions that return scalar values are allowed in these locations:

  • As an expression in the select_list of a SELECT statement:

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
    StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
GO

  • As an expression or string_expression in a WHERE or HAVING clause predicate:

    USE AdventureWorks2008R2;
GO
SELECT ProductID, ListPrice, StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
ORDER BY ProductID, StartDate;
GO

  • As a group_by_expression in a GROUP BY clause.

  • As an order_by_expression in an ORDER BY clause.

  • As an expression in the SET clause in an UPDATE statement:

    USE AdventureWorks2008R2;
GO
UPDATE Production.ProductListPriceHistory
SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
WHERE ProductID > 900;
GO

  • As an expression in the VALUES clause of an INSERT statement:

    User-defined functions referenced in these locations are logically executed one time per row.

CHECK Constraints

User-defined functions that return scalar values can be invoked in CHECK constraints if the argument values passed to the function reference columns only in the table or constants. Every time the query processor checks the constraint, the query processor calls the function with the argument values associated with the current row being checked. The owner of a table must also be the owner of the user-defined function invoked by a CHECK constraint on the table.

DEFAULT Definitions

User-defined functions can be invoked as the constant_expression of DEFAULT definitions if the argument values passed to the function only contain constants. The owner of the table must also be the owner of the user-defined function invoked by a DEFAULT definition on the table.

Computed Columns

Functions can be invoked by computed columns if the argument values passed to the function reference only columns in the table or constants. The owner of the table must also be the owner of the user-defined function invoked by a computed column in the table.

Assignment Operators

Assignment operators (left_operand = right_operand) can invoke user-defined functions that return a scalar value in the expression specified as the right operand.

Control-of-Flow Statements

User-defined functions that return scalar values can be invoked by control-of-flow statements in their Boolean expressions.

CASE Expressions

User-defined functions that return a scalar value can be invoked in any of the CASE expressions.

User-defined functions that return a character string can be invoked as the string_expr expression of PRINT statements.

Functions and Stored Procedures

  • Function arguments can also be a reference to a user-defined function that returns a scalar value.

  • RETURN integer_expression statements in stored procedures can invoke user-defined functions that return an integer as the integer_expression.

  • RETURN return_type_spec statements in user-defined functions can invoke user-defined functions that return a scalar data type such as the return_type_spec, provided the value returned by the invoked user-defined function can be implicitly converted to the return data type of the invoking function.

Executing User-Defined Functions That Return a Scalar Value

You can execute user-defined functions that return scalar values in the same manner as stored procedures. When executing a user-defined function that returns a scalar value, the parameters are specified in the same way they are for stored procedures:

  • The argument values are not enclosed in parentheses.

  • Parameter names can be specified.

  • If parameter names are specified, the argument values do not have to be in the same sequence as the parameters.

The following example creates a user-defined function that returns a decimal scalar value.

IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

The following example executes the dbo.ufn_CubicVolume function. Using the Transact-SQL EXECUTE statement, the arguments are identified in an order different from the parameters in the function definition:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO

The following example executes the dbo.ufn_CubicVolume function without specifying the parameter names:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume 12.3, 4.5, 4.5;
SELECT @MyDecimalVar;
GO

You can also use the ODBC CALL syntax to execute the dbo.ufn_CubicVolume function from OLE DB or ODBC applications:

-- First use SQLBindParam to bind the return value parameter marker
-- to a program variable of the appropriate type
SQLExecDirect(hstmt,
              "{ CALL ? = dbo.ufn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);

Invoking User-Defined Functions That Return a table Data Type

You can invoke a user-defined function that returns a table where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. An invocation of a user-defined function that returns a table can be followed by an optional table alias. The following example illustrates calling the table-valued function dbo.ufnGetContactInformation in the FROM clause of a SELECT statement.

USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

When a user-defined function that returns a table is invoked in the FROM clause of a subquery, the function arguments cannot reference any columns from the outer query.

Static, read-only cursors are the only type of cursor that can be opened on a SELECT statement whose FROM clause refers to a user-defined function that returns a table.

A SELECT statement that references a user-defined function that returns a table invokes the function one time.

Invoking Built-in Table-Valued Functions

There are several built-in table-valued functions that return a table value. The invocation of these built-in user-defined functions can be either unqualifed or can use the sys schema qualifier. You should use the sys schema qualifier for built-in table-valued functions, because it prevents conflicts with user-defined functions of the same name. The following example shows how to invoke the system built-in function fn_helpcollations.

SELECT *
FROM sys.fn_helpcollations();
GO

Using Hints with Table-Valued Functions

When you create a user-defined function, you can apply a table hint in any queries that form the function definition. Hints applied to views that reference Transact-SQL table-valued functions are also applied to the functions. These functions can conflict with the hints in the function definition. For more information, see View Resolution.

You cannot apply hints on views that reference CLR table-valued functions.

Note

The ability of the Database Engine to apply hints on views to multi-statement table-valued functions that are part of the view definition will be removed in a future version of SQL Server.

You cannot apply a table hint to the result of any table-valued function in the FROM clause of a query.