In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements. The SQL 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 comprising zero or more rows, 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
RETURN (Celsius * 1.8) + 32
Once created, a user-defined function may be used in 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.
Please refer below link for more details:
http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx
Please refer below link for more details:
http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx
