Making Extended Procedures Easier to Use

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):

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.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

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