- New Advances in SQL Server Functions
- User-Defined Functions
- Functions That Return a Table
- Calling User-Defined Functions
- Function Determinism
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Calling User-Defined Functions
Calling or invoking a user-defined function is quite easy. All you have to do is specify the name of the function you are calling, followed by a set of parentheses. Within the parentheses, you must specify any parameters being passed into the function. Of course, all parameters that you pass in must be in the same order as they are defined in the function.
Calling Scalar Functions
As mentioned previously, scalar functions can be used in T-SQL statements in any location that accepts the data type that the function returns. The following is a list of where you can use scalar functions:
-
In T-SQL queries, scalar functions are allowed in any of the following locations:
-
In the SELECT clause of a SELECT statement
-
In a WHERE or HAVING clause
-
In a GROUP BY clause
-
In an ORDER BY clause
-
In the SET clause of an UPDATE statement
-
In the VALUES clause of an INSERT statement
-
Scalar functions can be used in CHECK constraints if the columns referenced in the function are contained in the table that contains the CHECK constraint.
-
DEFAULT constraints can use scalar functions if the parameters passed into the function are constants.
-
Computed columns that contain functions can reference only other columns in the table or constants.
-
Scalar functions are allowed as right operators in assignment operations (value1 = value2).
-
Control-of-flow statements are allowed to use scalar functions in their Boolean expressions.
-
Case statements can invoke functions in any part of the statement.
-
PRINT statements can use functions that return character strings.
-
Other functions can reference functions that return scalar values.
-
The RETURN statement can use any scalar function that returns an integer value.
Calling Tabular Functions
Tabular functions are allowed only in the FROM clause of SELECT, UPDATE, INSERT, and DELETE statements. A couple of rules must be followed during the use of tabular functions:
-
If a user-defined function is referenced in the FROM clause of a subquery, the arguments of the function can't reference any columns in the outer query.
-
If a cursor is being opened based on the results of the execution of a tabular function, the cursor must be declared as a static, read-only function.
Calling Built-in Functions
SQL Server 2000 provides several built-in functions that you can use. Built-in functions that return tables must be prefaced with two colons (::), which distinguish them from any user-defined functions. You also don't use any database or owner name with this type of function. If you are calling a scalar function, you need to use only the one-part name of the function—you don't need to use the leading two colons. An example of a built-in table that returns a table is as follows:
SELECT * FROM ::fn_helpcollations()