User Defined Function Article Index for
User 		Website Links For
Function 		 

Information About

User Defined Function
  CATEGORIES ABOUT USER-DEFINED FUNCTION
   
computer programming
   
databases
   
sql
   
APPAREL
BABY
BEAUTY
BOOKS
CAR TOYS
CELL PHONES
DVD'S
ELECTRONICS
GOURMET FOOD
GROCERIES
HEALTH & PERSONAL
HOME & GARDEN
JEWELRY
MUSIC
MUSIC INSTRUMENTS
OFFICE PRODUCTS
SOFTWARE
SPORTING GOODS
TOOLS & HARDWARE
TOYS
VIDEO GAMES
SHOPPING HOME
MORE SHOPPING...




BASIC LANGUAGE


In some old implementations of the BASIC programming language, user defined functions are defined using the "DEF FN" syntax. More modern dialects of BASIC are influenced by the Structured Programming paradigm, where most or all code is written as user defined functions or procedures, and the concept becomes practically redundant.


DATABASES


In SQL databases, a user-defined function provides a mechanism for standard distinguishes between scalar and table functions. A scalar function returns only a single value (or NULL ), whereas a table function returns a (relational) table comprised of zero or more rows and each row with one or more columns.

User-defined functions in SQL are declared using the CREATE FUNCTION statement. For example, a function that converts Celsius to Fahrenheit might be declared like this:



CREATE FUNCTION dbo.CtoF(Celsius FLOAT)

  RETURNS FLOAT


    

  •  1.8) + 32

    •


Once created, a user-defined function may be used as Expressions in SQL statements. For example, it can be invoked where most other intrinsic functions are allowed. This also includes SELECT statements, where the function can be used against data stored in tables in the database. Conceptually, the function is evaluated once per row in such usage. For example, assume a table named ELEMENTS, with a row for each known chemical element. The table has a column named BoilingPoint for the boiling point of that element, in Celsius. This query:



SELECT Name, CtoF(BoilingPoint)

  FROM Elements


would retrieve the name and the boiling point from each row. It invokes the CtoF user-defined function as declared above in order to convert the value in the column to a value in Fahrenheit.

Each user-defined function carries certain properties or characteristics. The SQL standard defines the following properties:
  • language - defines the programming language in which the user-defined function is implemented; examples are SQL, C, or Java.

  • parameter style - defines the conventions that are used to pass the function parameters and results between the implementation of the function and the database system (only applicable if language is not SQL).

  • specific name - a name for the function that is unique within the database. Note that the function name does not have to be unique, considering overloaded functions.

  • determinism - specifies whether the function is deterministic or not. The determinism characteristic has an influence on the Query Optimizer when compiling a SQL statement. The rewrite of SQL statements may not change the number of function invocations for non-deterministic functions.

  • SQL-data access - tells the database management system whether the function contains no SQL statements (NO SQL), contains SQL statements but does not access any tables or views (CONTAINS SQL), reads data from tables or views (READS SQL DATA), or actually modifies data in the database (MODIFIES SQL DATA).


User-defined functions should not be confused with Stored Procedure s. Stored procedures allow the user to group a set of SQL commands. A procedure can accept parameters and execute its SQL statements depending on those parameters. A procedure is not an expression and, thus, cannot be used like user-defined functions.

Some database management systems allow the creation of user defined functions in languages other than SQL. Microsoft SQL Server , for example, allows the user to use .NET Languages for this purpose. DB2 and Oracle support user-defined functions written in C or Java programming languages.


SQL Server 2000

There are three types of UDF in Microsoft SQL Server 2000 :
#Scalar functions.
#Inline table-valued functions.
#Multistatement table-valued functions.

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.
Inline table-valued functions return the result set of a single SELECT statement.
Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements.

User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.


EXTERNAL LINKS