for RuBoard |
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):
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.
for RuBoard |