The syntax for creating a scalar UDF is fairly straightforward, as the following pseudocode shows:
CREATE FUNCTION FunctionName
( InputParam1 datatype = DefaultValue, InputParam2...)
RETURNS datatype
AS
BEGIN
RETURN (expression)
END
For the solution to this example, you need two input parameters—a datetime value and a separator (defaulting to a colon for ease of use)—a string of 10 characters to be returned, and the expression you worked out in Listing 3. Listing 4 shows the resulting function.
Note that the code explicitly owner-qualifies this function. Although you don't need to owner-qualify on creation, you must owner-qualify on use. Because you can use functions as part of your expression in the SELECT list, Microsoft decided to preventatively minimize collisions with the company's future built-in functions. For example, if you created a function named Median, then Microsoft released a system function called Median, you would experience namespace collisions. Therefore, you must explicitly owner-qualify your UDFs to guarantee that you'll always return your function's value instead of any future built-in function's value. Also, owner-qualifying on execution—whether you use a function, stored procedure, or view—is always good practice. Owner-qualifying can lead to better performance because you waste less time on lookups that fail because the owner is ambiguous.
After you've created the function, you can flexibly use it where you used to enter the complex expression you created for SQL Server 7.0 and earlier releases. You can use the three examples in Listing 5 to test the new function. In these examples, notice how the DEFAULT value was requested for the second parameter. Although you don't have to furnish parameters when you use stored procedures, you must explicitly supply parameters for functions, using placeholders of DEFAULT for default values. The code in Listing 5 shows a few ways that you can use this function. The first example uses the current date as input and the default value for the separator. The second example uses an exclamation mark as the separator along with the current date. The last example, which uses values from the pubdate column of the Pubs titles table, produces a result set in which the dates are formatted with colons.
Scalar UDFs give you a way to encapsulate simple expressions and easily reuse them. These functions are similar to stored procedures in their code, yet their usage is identical to that of system functions. In an upcoming column, I'll continue to explore the manipulation of datetime data by showing you more examples of scalar UDFs.
End of Article
Prev. page
1
2
[3]
next page -->
