Extended Stored Procedures: Intro And 10 Cool Examples
In this article Joe takes us through what an SQL Server 2000 extended stored procedure is. He also shows us how to use 10 of SQL Server 2000's most coolest extended stored procedures to accomplish tasks such as killing a system process, writing to the event log, encrypting data to a database, parsing XML, shelling to the command-line, and more.
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:
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:
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:
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
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)
print 'Numbers are the same'
print 'Numbers are different'
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".