Content deleted Content added
m linking |
m Task 70: Update syntaxhighlight tags - remove use of deprecated <source> tags |
||
Line 8:
User-defined functions in SQL are declared using the <code>CREATE FUNCTION</code> 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
</syntaxhighlight>
Once created, a user-defined function may be used in [[expression (programming)|expressions]] in SQL statements. For example, it can be invoked where most other intrinsic functions are allowed. This also includes [[Select (SQL)|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. The query
<
SELECT Name, CtoF(BoilingPoint)
FROM Elements
</syntaxhighlight>
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.
Line 46:
1. On Microsoft SQL Server 2000 a table-valued function which "wraps" a View may be much faster than the View itself. The following MyFunction is an example of a "function-wrapper" which runs faster than the underlying view MyView:
<
CREATE FUNCTION MyFunction()
RETURNS @Tbl TABLE
Line 76:
RETURN
END
</syntaxhighlight>
2. On Microsoft SQL Server 2005 the result of the same code execution is the opposite: view is executed faster than the "function-wrapper".
Line 87:
Errors in UDF cause UDF to abort which, in turn, aborts the statement that invoked the UDF.
<
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
Line 100:
RETURN(@CubeLength * @CubeWidth * @CubeHeight)
END
</syntaxhighlight>
Data type supported in Microsoft SQL Server 2000
| |||