How to Implement User Defined Function in SQL

Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as user-defined functions (UDFs). A UDF is a database object that contains a set of T-SQL statements, accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

UDFs have a limited scope as compared to stored procedures. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function.

UDFs are of different types: scalar functions and table-valued function. As a database developer, it is important for you to learn to create and manage different types of UDFs.

Creating UDFs

A UDF contains the following components:

  • Function name with optional schema/owner name
  • Input parameter name and data type
  • Options applicable to the input parameter
  • Return parameter data type and optional name
  • Options applicable to the return parameter
  • One or more T-SQL statements

To create a function, you can use th CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS ] [ type_schema_name. ]
Parameter_data_type] }
[ = default ] }
[, …n ]
RETURNS return_data_type
[WITH <function_option> [ , . . .n ] ]
[ AS ]
RETURN expression

  • Schema_name is the name of the schema to which the UDF belongs.
  • Function_name is the name of the UDF. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
  • @parameter_name is a parameter in the UDF. One or more parameters can be declared.
  • [type_schema_name.] parameter_data_type is the data type of the parameter, and optionally the schema to which it belongs.
  • [=default ] is a default value for the parameter.
  • Return_data_type is the return value of a scalar user-defined function.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google