Home > Articles > Data > SQL Server

  • Print
  • + Share This
From the author of

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()
  • + Share This
  • 🔖 Save To Your Account