Extended Stored Procedures: Intro And 10 Cool Examples - Useful Extended Stored Procedures

(Page 3 of 6 )

It's time to take a look at 10 extended stored procedures that can help make your coding of SQL procedures 100 times easier. Each extended stored procedure shown below includes an example, and also why you'd want to use that extended stored procedure when you're creating SQL procedures or batches.

Proc #1: xp_fileexist

If you're only interfacing with SQL Server via ADO in a language such as VB or C++, then both of these languages have functions you can use to check whether or not a file exists. However, if you're like me and love to create your SQL procs in 100% TSQL, then you'll be glad to know that you can check whether or not a file exists using the xp_fileexist extended stored procedure. Its signature looks like this:

xp_fileexist filename [, <file_exists INT> OUTPUT]

The second parameter is optional, and can be used to return the procs value into an integer variable. To return a record containing the details of whether or not the file exists, use the following command:

exec master..xp_fileexist 'c:\test.file'

It returns the following record (assuming the file exists):



To return the value of the xp_fileexist extended store procedure into a variable, we do so like this:

declare @exists int

exec master..xp_fileexist 'c:\test.file', @exists output

print @exists

Proc #2: xp_regread

I was actually quite surprised about this extended stored procedure a couple of months back. It reads in a value from the system registry and returns it as a record. Its signature looks like this:

xp_regread root_key, path_key, key_value

As you should know, the registry includes thousands upon thousands of system variable values, program extensions and versions, directory locations, etc. To retrieve the name of my windows partition from the registry I would use xp_regread like this:

master..xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\Setup', 'SystemPartition'

Proc #3: xp_repl_encrypt

This handy extended stored procedures allows us to one-way encrypt string of data using an SQL Server-defined algorithm. Why would anyone want to encrypt without being able to decrypt you ask? The answer's quite simple actually. In a recent scenario, I had to store credit card numbers in a database. Being the security buff that I am, I felt opposed to doing so, but I was left with no choice: either store the credit card numbers, or forfeit the job.

I stumbled across the xp_repl_encrypt extended stored procedure and used it to store the credit card numbers in a database. It worked extremely well. Here's a quick example of how you could use the xp_repl_encrypt extended store procedure in your daily doings:

create proc sp_TestCCNum

@cardNumber1 varchar(180)

as

declare @cardNumber2 varchar(180)

set @cardNumber2 = '4111111111111111'

exec master..xp_repl_encrypt @cardNumber1 output

exec master..xp_repl_encrypt @cardNumber2 output

-- @cardNumber1 and @cardNumber2 are now encrypted

if(@cardNumber1 = @cardNumber2)

begin

print 'Numbers are the same'

end

else

begin

print 'Numbers are different'

end

go

exec sp_TestCCNum '4111111111111111'

The stored procedure shown above is called sp_TestCCNum and accepts one parameter, a credit card number in the form of a string. It checks whether the encrypted version of that number is the same as the encrypted version of a pre-defined credit card number (which you would normally grab from a database). If they are, then it prints out "Numbers are the same". If not, it prints out "Numbers are different".

Next: Examples (contd.) >>
 

More SQL Server Articles
More By Joe O'Donnell

Comments On: Extended Stored Procedures: Intro And 10 Cool Examples

>>> Be the FIRST to comment on this article!