This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
.gif)
How to Write Your Own System Functions
Scott Whigham
Most of us are familiar with SQL Server 2000's support for UDFs (user-defined functions). But how many of us have extended the notion of the basic UDF and created our own system function? If you're curious, read on as Scott Whigham shows you how.
It seems to me that our ability to create our own system functions is an overlooked new feature in SQL Server 2000. Much like system stored procedures, system functions exist in the Master database and are usable from any database on the server. In fact, many of the administrative stored procedures you may have written as system stored procedures could likely be morphed into system functions without too much fuss. In earlier versions of SQL Server, I tended to use several system stored procedures that either I or someone else wrote that I happened to find useful. With SQL Server 2000, I can move much of this stored procedure logic into a function that, frankly, gives me additional flexibility and ease of use. In this article, I'm going to assume you have a basic knowledge of the three types of functions (rowset, aggregate, and scalar) available in SQL Server 2000. If you need to brush up on creating or altering functions, type in "CREATE FUNCTION" in the Index of SQL Server 2000's Books Online (BOL).
System functions are called the same way you call any other function that you've created. You can call them inline or as you would a table-valued function. In fact, system functions adhere to the same rules and regulations that the three types of user-defined functions must abide by. As with normal user-defined functions (UDFs), you must type in the parentheses after listing your function, as in fn_scottsfunction().
System functions do have a few special rules associated with them. First, all system functions must be owned by the owner "system_function_schema." This is a new user that ships with SQL Server 2000 and exists in the master database. However, you won't be able to see this user account through Enterprise Manager; to see this user, it's best to query the sysusers table in the master database. On my server (and every other server I've seen), "system_function_schema" is UID 4.
To find out the UID of the "system_function_schema" user on your SQL Server, run the following query:
SELECT UID FROM master.dbo.sysusers
WHERE "NAME" = 'system_function_schema'
To see a list of all objects that this UID owns, run this query:
SELECT * FROM master.dbo.sysobjects WHERE UID =
(SELECT UID FROM master.dbo.sysusers
WHERE "NAME" = 'system_function_schema')
Another rule to follow when creating system functions is to make sure they begin with "fn_"—as in fn_helpcollations(). (Normal UDFs don't have this requirement.) Another quirky piece of info is that the function name must be all lowercase letters and/or numbers with no spaces. The function name fn_ScottsFunction() is invalid while fn_scottsfunction() is valid.
Now that we know most of the rules, let's put some of these to work. Here's an example that most of us have used for from time to time. It's a reworking of the old "Proper" function provided in other programming languages and systems. The object is to take the first letter of a word, capitalize this letter, and then make the rest of the characters in that word lowercase (or make the word "Proper" case). The first example is how to write this as a stored procedure. Notice I used an OUTPUT parameter. To use this in an application, I could then take the OUTPUT local variable and populate a column during an INSERT or UPDATE with this.
CREATE PROC sp_Proper (@InputString VARCHAR(255),
@OutputString VARCHAR(255) OUTPUT)
AS
SELECT @OutputString =
UPPER(SUBSTRING(@inputString,1,1))
+ LOWER(SUBSTRING(@InputString,2,254))
GO
DECLARE @OutputString VARCHAR(255)
EXEC sp_Proper 'scott', @OutputString OUTPUT
SELECT @OutputString
Now let's take a look at using a system function. This will be a scalar function and, as such, must adhere to all of the rules for scalar functions.
EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTERGO
CREATE FUNCTION
system_function_schema.fn_proper(@InputString
VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE @ReturnString varchar(350)
SELECT @ReturnString =
UPPER(SUBSTRING(@inputString,1,1)) +
LOWER(SUBSTRING(@InputString,2,254))
RETURN(@ReturnString)
END
GO
EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
SELECT fn_proper('scott')
The first thing you'll probably notice is that I had to run SP_CONFIGURE to allow updates to the system catalogs. This is another requirement for creating system functions. As you can see, the logic inside the system function is no different from the logic inside a "normal" UDF. Since this is a scalar function, we can use it anywhere an expression can be used, such as in the SELECT or WHERE clauses.
Now that you've seen how to move a system stored procedure into a system function, let's look at the difference in implementation. We'll start with a table titled "#TempTable," and, to make it easy for you to see the differences, we'll try inserting a row into "#TempTable" using both the system stored procedure and the system function.
--CREATE TABLE script
CREATE TABLE #TempTable (FirstColumn VARCHAR(255))
GO
Now let's look at how to INSERT a single row using our system stored procedure. First, we must declare our OUTPUT variable. Then we'll execute the procedure, and, finally, we can do our INSERT.
--INSERT with system stored procedure
DECLARE @OutputString VARCHAR(255)
EXEC sp_Proper 'scott', @OutputString OUTPUT
INSERT INTO #TempTable VALUES (@OutputString)
SELECT * FROM #TempTable
Three lines of code to do a single INSERT. To take advantage of our new system function, we could call it as follows:
-- INSERT with system function
INSERT INTO #TempTable VALUES (fn_proper('scott'))
SELECT * FROM #TempTable
Using a system function, our previous INSERT is reduced to a single line of code. And did you notice that we didn't have to refer to this scalar function by its two-part name? Since this is a system function, the two-part name isn't a requirement. And, as you know, because we created this as a system function, it can be referred to from within any database on the server.
You can create table-valued system functions as well. Take a look at some of your administrative stored procedures—chances are you'll see opportunities for migrating many of them into system functions as well.
Download SYSFUNCS.SQL
To find out more about SQL Server Magazine and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the December 2001 issue of SQL Server Magazine. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Magazine is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.