-->

Wednesday, October 29, 2014

How to Implement User Defined Function in SQL

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 ]
BEGIN
Function_body
RETURN expression
END
[;]
Where,

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


Read other related articles

Also read other articles

© Copyright 2013 Computer Programming | All Right Reserved