Home arrow SQL Server arrow Page 4 - 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 - Examples (contd.)
(Page 4 of 6 )

Proc #4: xp_dirtree

Sometimes it's handy to have access to the directory structure of the file system through SQL Server. The xp_dirtree is a handy extended store procedure that traverses a directory and returns the details of all of its sub-directories as records. Its signature looks like this:

xp_dirtree parent_directory

To test this extended stored procedure, jump to the DOS prompt (Yes, I know it's old school, but I'm an old school kinda guy) and create some directories like this:

C:\>md mydir

C:\>cd mydir

C:\mydir>md subdir1

C:\mydir>md subdir2

C:\mydir>cd subdir1

C:\mydir\subdir1>md subsubdir1

Enter the following line into Query Analyzer:

exec master..xp_dirtree 'c:\mydir'

After running the query, the results in the output window look like this:

Results of the xp_dirtree function

As you can see from the results set above, each sub-directories name is returned along with a depth value, which is relative to the directory passed to the extended stored procedure. For example, if I passed in c:\blah and that directory contained another directory named c:\blah\blah1, then blah1's depth would be one, because it's one directory level deep.

Proc #5: sp_xml_preparedocument

The sp_xml_preparedocument extended stored procedure takes an XML document, parses it using the MSXML2 parser, and provides the parsed document, which is now ready for consumption using SQL Servers various other XML-related procedures. The signature of the sp_xml_preparedocument extended stored procedure looks like this:

sp_xml_preparedocument hdoc OUTPUT [, xmltext] [, xpath_namespaces]

The first parameter, hdoc, is an integer handle that is used to identify the XML document to SQL Server. The second parameter, xmltext, is the actual XML document that SQL Server should tell the MSXML2 library to parse, and the third option is a set of OPENXML XPath namespaces that can be used to format the resultant XML document.

To test the sp_xml_preparedocument extended stored procedure, enter the following code into Query Analyzer:

declare @id int

declare @xml varchar(500)

set @xml = '<people><person name="John Doe" age="30"/></people>'

exec sp_xml_preparedocument @id output, @xml

select * from OpenXML(@id, '/people/person')

with(name varchar(50) '@name')

exec sp_xml_removedocument @id

The code above creates a simple XML string containing a root element and one child element. We use the sp_xml_preparedocument extended stored procedure to load and parse the XML string, returning the ID of the internal representation of the XML document to @id.

To actually retrieve data from the XML, we use a normal select statement in combination with the OpenXML function, which takes the ID of the XML to load, as well as an XPath pattern that specifies the hierarchy to extract data from. The "with" expression tells SQL Server how to return the details of the XML, and the sp_xml_removedocument extended stored procedure removes the XML representation referred to by @id from memory.

The output from running the SQL batch shown above looks like this:

Output from the code described above

Proc #6: xp_availablemedia

As you might've guessed, if an SQL Server isn't setup and configured properly, then several extended stored procedures can compromise the stability of that server. The xp_availablemedia extended stored procedure as well as a couple of others can do just that.

The xp_availablemedia extended stored procedure is used to return a list of available storage devices that can be written to. It doesn't require any special signature definitions, because all you have to do to use it is call it directly, with no parameters.

Use the following line to get a list of all available storage devices:

exec master..xp_availablemedia

On my SQL Server, the results set looked like this:

The results of a call to xp_availablemedia

Proc #7: xp_getnetname

The xp_getnetname extended stored procedure returns the NetBIOS name of the current system. It accepts one optional parameter and its signature looks like this:

xp_getnetname [@netname [var]char(MAX_COMPUTERNAME_LENGTH_ OUT]

To print out the NetBIOS name of my SQL Server, I used the xp_getnetname extended stored procedure like this:

declare @netname varchar(20)

exec master..xp_getnetname @netname output

print 'The name of my SQL Server is ' + @netname

On my system, the output from the SQL code above is "The name of my SQL Server is SQLSERVER".
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-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials