The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

Creating Your Own System Functions

You'll recall the discussion in Chapter 9 regarding creating our own system viewsviews that can be queried from any database and run within the context of that database. You can do something similar with system functions. You can create functions that reside in master, but that can be queried from any database without a database name prefix.

Here's how it works: SQL Server creates a number of system UDFs during installation (e.g., fn_varbintohexstr(), fn_chariswhitespace(), and so on). Some of these are owned by the system_function_schema, and some aren't. Those that are owned by system_function_schema can be accessed from any database using a one-part name. You can find out which ones these are by running the query in Listing 10-9:

Listing 10-9 SQL Server's system functions.

USE master
GO
SELECT name
FROM sysobjects
WHERE uid=USER_ID('system_function_schema')
AND   (OBJECTPROPERTY(id, 'IsScalarFunction')=1
OR OBJECTPROPERTY(id, 'IsTableFunction')=1
OR OBJECTPROPERTY(id, 'IsInlineFunction')=1)

(Results)

name
--------------------------------------------------
fn_chariswhitespace
fn_dblog
fn_generateparameterpattern
fn_getpersistedservernamecasevariation
fn_helpcollations
fn_listextendedproperty
fn_removeparameterwithargument
fn_replbitstringtoint
fn_replcomposepublicationsnapshotfolder
fn_replgenerateshorterfilenameprefix
fn_replgetagentcommandlinefromjobid
fn_replgetbinary8lodword
fn_replinttobitstring
fn_replmakestringliteral
fn_replprepadbinary8
fn_replquotename
fn_replrotr
fn_repltrimleadingzerosinhexstr
fn_repluniquename
fn_serverid
fn_servershareddrives
fn_skipparameterargument
fn_trace_geteventinfo
fn_trace_getfilterinfo
fn_trace_getinfo
fn_trace_gettable
fn_updateparameterwithargument
fn_virtualfilestats
fn_virtualservernodes

Chapter 22 covers creating system functions as well as other types of system objects. For now, just understand that because they belong to system_function_schema, they can be called across databases using one-part names.

To create your own system UDF, follow these steps:

Enable updates to the system tables. You can do that with this code:

sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE

Create the function in master, being sure to owner-qualify it. The function name must begin with fn_ and must be entirely in lowercase.

Disable updates to the system tables (as a rule, you should leave the allow updates switch turned off, especially on production systems):

sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE

Here's an example of couple of very simple system UDFs (Listing 10-10):

Listing 10-10 Two user-defined system functions: fn_greatest() and fn_least().

USE master
GO
exec sp_configure 'allow updates',1
GO
reconfigure with override
GO
DROP FUNCTION system_function_schema.fn_greatest,
system_function_schema.fn_least
GO
CREATE FUNCTION system_function_schema
.fn_greatest(@x bigint, @y bigint)
RETURNS bigint
AS
BEGIN
RETURN(CASE WHEN @x>@y THEN @x ELSE @y END)
END
GO
CREATE FUNCTION system_function_schema.
fn_least(@x bigint, @y bigint)
RETURNS bigint
AS
BEGIN
RETURN(CASE WHEN @x<@y THEN @x ELSE @y END)
END
GO
exec sp_configure 'allow updates',0
GO
reconfigure with override
GO
use northwind
GO
SELECT fn_greatest(2156875324698752,
2156875323698752), fn_least(989, 998)

(Results)

DBCC execution completed. If DBCC 
printed error messages, contact your system
administrator.
Configuration option 'allow updates' 
changed from 0 to 1. Run the RECONFIGURE
statement to install.
DBCC execution completed. If DBCC 
printed error messages, contact your system
administrator.
Configuration option 'allow updates' 
changed from 1 to 0. Run the RECONFIGURE
statement to install.
-------------------- --------------------
2156875324698752     989

Here we've created a couple of new system functions: fn_greatest() and fn_least(). These correspond to Oracle's GREATEST and LEAST functions. They return the larger or smaller of two 8-byte integers. You can create your own system UDFs by following the steps listed earlier. Note that table-valued system UDFs must be qualified with a double colon regardless of whether you created the function or it was created during the installation process.