Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter


Server querying SPís

The system database ďmasterĒ has many sources of information about the server. They can be either system SPís or system functions. A special type of system functions is the configuration functions, whose names always start with @@. The information is returned in a rowset.


Example: Create an SP that will return the Service Pack version of SQL Server.




--Example of an SP that queries the server, it returns the Service Pack version of SQL Server


      WHEN '8.00.760' THEN 'SP3'

      WHEN '8.00.534' THEN 'SP2'

      WHEN '8.00.384' THEN 'SP1'

      WHEN '8.00.194' THEN 'NO SP'

      ELSE 'Unknown'

END AS SPVersion


SPís returning rows from an XP

An SP can return a rowset created by an XP or store the XP scalar output and return it in a SELECT statement. The XP can be either a system XP or a user XP.


Example: Create an SP that will return the output from ipconfig, using the system XP xp_cmdshell.




--returns the output from ipconfig using a shell to the operating system

EXEC master..xp_cmdshell 'ipconfig'

--shell to the operating system and execute the ipconfig command


SPís returning rows from a crafted SELECT statement

An SP can return a rowset based on its input. Sometimes the output of an SP cannot be returned in a output parameter because it is more than one value.


Example: Create an SP that will return each word from a sentence as one row.


CREATE PROCEDURE StringToRows @strInput varchar(8000)

--Return each word from a sentence as one row


DECLARE @counter int--declare counter

DECLARE  @tmpTable TABLE(line varchar(100))--declare temporary table for output

SET @strInput=LTRIM(RTRIM(@strInput))--remove leading and trailing spaces

WHILE CHARINDEX('  ',@strInput)>0

      SET @strInput=REPLACE(@strInput, '  ', ' ')--remove extra spaces

print @strInput

SET @counter=CHARINDEX(' ',@strInput)--set counter

WHILE @counter>0--loop through input string


      INSERT INTO @tmpTable--insert data into table

               VALUES(LEFT(@strInput, @counter-1))

      SET @strInput=RIGHT(@strInput, LEN(@strInput)-@counter)--remove inserted data from the input

      SET @counter=CHARINDEX(' ',@strInput)--update counter


IF LEN(@strInput)>0--if there is still some data left

      INSERT INTO @tmpTable--insert data into table


SELECT line FROM @tmpTable--return rowset

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter