Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
  SQL Server Tips by Gama and Naughter

 

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.

 

CREATE PROCEDURE SPipconfig

AS

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

AS

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

      BEGIN

      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

      END

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

      INSERT INTO @tmpTable--insert data into table

               VALUES(@strInput)

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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

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