Invoking User-Defined Functions That Return a Scalar Value

Accessing and Changing Relational Data

previous page next page
Accessing and Changing Relational Data

Invoking User-Defined Functions That Return a Scalar Value

You can invoke a user-defined function that returns a scalar value anywhere a scalar expression of the same data type is allowed in Transact-SQL statements:

Queries

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

  • As an expression in the select_list of a SELECT statement: 
    SELECT *, dbo.fn_CalculateDaysLate(RequiredDate) AS DaysLate
FROM Northwind.dbo.Employees
  • As an expression or string_expression in a WHERE or HAVING clause predicate: 
    SELECT *
FROM Northwind.dbo.[Order Details]
WHERE UnitPrice < dbo.fn_MeanUnitPrice()
  • 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: 
    UPDATE Orders
 SET ShipVia = dbo.fn_FindLeastCostShipper(ShipCity)
 WHERE OrderID = 10274
  • As an expression in the VALUES clause of an INSERT statement: 
    INSERT INTO Shippers
   VALUES (4, dbo.fn_GetShipperName(), n'(503)555-9931'

    User-defined functions referenced in these locations are logically executed once 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. Each time the query processor checks the constraint, 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 contains only 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.

PRINT statements

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 as 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.

This is a definition of a user-defined function that returns a decimal:

CREATE FUNCTION fn_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

This is an example of executing the fn_CubicVolume function. Using the Transact-SQL EXECUTE statement, the arguments are identified in a different order than the parameters in the function definition:

DECLARE @MyDecimalVar decimal(12,3)
EXEC @MyDecimalVar = dbo.fn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5

This is an example of executing the fn_CubicVolume function without specifying the parameter names:

DECLARE @MyDecimalVar decimal(12,3)
EXEC @MyDecimalVar = dbo.fn_CubicVolume 12.3, 4.5, 4.5

You can also use the ODBC CALL syntax to execute the fn_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 ? = fn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);
previous page start next page