Rolling Your Own System Routines

You've seen that SQL Server 2000 provides both system-level stored procedures and system-level table-valued functions. The two types of objects are similar in that they can be invoked from any database even though they are stored only in the master database.

Your Own System Procedures

All the system stored procedure names begin with sp_. This is more than just a convention. Any procedure created in the master database that begins with sp_ can be called from any other database without having to be fully referenced with the database name. This can also be useful for procedures you create. The sp_ magic works even for extended stored procedures, which are user-written calls to DLLs. By convention, extended stored procedure names begin with xp_, but the sp_ prefix and its special property can be applied to them as well (but only when added to the master database). In fact, some extended procedures that are supplied as part of the product, such as those used to create Automation objects (for example, sp_OACreate), use the sp_ prefix so that they can be called from anywhere, even though they're actually functions in a DLL, not a Transact-SQL stored procedure.

The sp_ prefix actually makes a procedure special in two ways. First, as I've mentioned, a procedure whose name starts with sp_ can be called directly from any database, without a fully qualified name. Second, system tables referenced in your special procedures will always refer to the tables in the database from which the procedure was called. For example, the sp_help stored procedure lists all the objects from the sysobjects system table. But every database has its own sysobjects table, so which one is used? If you execute sp_help from the pubs database, you get a list of the objects in pubs; if you call sp_help from msdb, you get a list of objects in msdb, and so on. Two tables are exceptions to this rule. For some undetermined reason, if your system stored procedure references the sysfiles or sysfilegroups table, the procedure will always access the table from the master database even though there is a sysfiles and sysfilegroups table in every database.

The following example illustrates this behavior:

 CREATE PROC sp_list_files_and_objects AS SELECT filename FROM sysfiles SELECT name from sysobjects RETURN 

I won't show you the output from executing the procedure because the list of objects will probably be quite long no matter what database you run this procedure from. But you can try it yourself:

 USE pubs EXEC sp_list_files_and_objects USE northwind EXEC sp_list_files_and_objects 

You'll always see the same list of filenames, which are the ones for the master database, but the list of objects will change depending on your current database.

Of course, if the sp_ procedure references a table that exists only in the master database, such as sysconfigures, the table in master will be referenced no matter where you are when you call the procedure. This trick works only for system tables, however. If you create a user table called MasterLookup and reference it in an sp_ stored procedure, SQL Server will look only in the master database to try to find the MasterLookup table. If it doesn't exist in master, you'll get an error message when executing your procedure, even if you call the procedure from the database where the table is located.

WARNING


Microsoft strongly discourages any direct references to the system tables and suggests that you instead use only the presupplied stored procedures and the object property functions to get any system information you need. It's not guaranteed that the structures of the system tables will remain the same from one release to the next, or even between service packs. In fact, the Upgrade Wizard for converting SQL Server 6 to SQL Server 2000 doesn't even attempt to upgrade any procedures that modify system tables.

I don't recommend that you use sp_ as a prefix for your own local stored procedures. The sp_ prefix has a special meaning, and unless you intend your procedure to have this special meaning and exist in the master database, it can be confusing for users who are aware of the special meaning.

Your Own System Functions

SQL Server 2000 also provides system functions that are treated in some ways just like the user-defined functions that you can write. These are the functions that are listed in the sysobjects table and that are listed when you select from the ROUTINES view. These system functions are different from the ones that you would normally create because they are available from any database. You saw the list of these functions, but I'll repeat it here:

 fn_helpcollations fn_listextendedproperty fn_servershareddrives fn_trace_geteventinfo fn_trace_getinfo fn_virtualfilestats fn_virtualservernodes 

Unlike the prefix for system stored procedures, the prefix fn_ by itself has no special meaning. In fact, I used it when creating my factorial function to distinguish my function from my procedure. In addition, it's not nearly as easy to create system functions of your own. Although it is possible, it is not supported at all, and you need to be aware that any system functions you write in one release might not work in a subsequent release.

The supplied system functions listed above all have the following characteristics:

  • They are objects in the master database.
  • Their names start with fn_.
  • They are owned by the user system_function_schema.
  • Their names use only lowercase letters.

If you want your function to be available from any database, it must have the characteristics listed above. However, it's not easy to create a function owned by system_function_schema. If you try, you get an error that there is no such user, even though that value is listed as a name in the sysusers table.

In order to create objects owned by the user name system_function_schema, you must enable updates to the system tables, which is why this technique is unsupported and undocumented. In addition, you cannot create a recursive system function. So, to create our factorial function as a system function, you have to use the iterative approach. Here's a complete example of creating my own system function:

 USE master GO EXEC sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO CREATE FUNCTION system_function_schema.fn_factorial (@param1 int) RETURNS NUMERIC(38, 0) AS BEGIN DECLARE @counter int, @result NUMERIC(38, 0) IF (@param1 < 0 OR @param1 > 33) RETURN (0) SET @counter=1 SET @result=1 WHILE (@counter < @param1 AND @param1 <> 0 ) BEGIN SET @result=@result * (@counter + 1) SET @counter=@counter + 1 END RETURN (@result) END GO EXEC sp_configure 'allow updates', 0 GO RECONFIGURE WITH OVERRIDE GO 

Now invoke it:

 USE <some_other_db> GO SELECT fn_factorial(5) 



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net