Home arrow SQL Server arrow Page 3 - Extended Stored Procedures: Intro And 10 Cool Examples

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.

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 134
February 11, 2002
  1. · Extended Stored Procedures: Intro And 10 Cool Examples
  2. · Extended Stored Procedures
  3. · Useful Extended Stored Procedures
  4. · Examples (contd.)
  5. · Examples (contd.)
  6. · Conclusion

print this article

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

Results from the xp_fileexist extended stored proc

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)


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".
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials