Making Extended Procedures Easier to Use
One technique for making an extended procedure a bit handier is to wrap it in a system procedure. This allows it to be easily called from any database context without having to be prefixed with master.. A good number of SQL Server's own extended procedures are wrapped in system stored procedures. Here's an example using the undocumented routine xp_varbintohexstr (Listing 20-15):
Listing 20-15 "Wrapping" extended procedures makes them easier to use.
USE master
IF (OBJECT_ID('dbo.sp_hexstring') IS NOT NULL)
DROP PROC dbo.sp_hexstring
GO
CREATE PROC dbo.sp_hexstring @int
varchar(10)=NULL, @hexstring varchar(30)=NULL OUT
/*
Object: sp_hexstring
Description: Return an integer as a hexadecimal string
Usage: sp_hexstring @int=Integer to convert,
@hexstring=OUTPUT parm to receive hex string
Returns: (None)
Created by: Ken Henderson. Email: khen@khen.com
Version: 1.0
Example: sp_hexstring 23, @myhex OUT
Created: 1999-08-02. Last changed: 1999-08-15.
*/
AS
IF (@int IS NULL) OR (@int = '/?') GOTO Help
DECLARE @i int, @vb varbinary(30)
SELECT @i=CAST(@int as int), @vb=CAST(@i as varbinary)
EXEC master..xp_varbintohexstr @vb, @hexstring OUT
RETURN 0
Help:
EXEC sp_usage @objectname='sp_hexstring',
@desc='Return an integer as a hexadecimal string',
@parameters='@int=Integer to convert,
@hexstring=OUTPUT parm to receive
hex string',
@example='sp_hexstring "23", @myhex OUT',
@author='Ken Henderson',
@email='khen@khen.com',
@version='1', @revision='0',
@datecreated='19990802', @datelastchanged='19990815'
RETURN -1
GO
DECLARE @hex varchar(30)
EXEC sp_hexstring 10, @hex OUT
SELECT @hex
(Results)
------------------------------
0x0000000A
Sp_hexstring validates the parameters to be passed to the extended procedure xp_varbintohexstr before calling it. Because sp_hexstring is a system procedure, it can be called from any database without referencing the extended stored procedure directly.