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


Rowset returning SP's

An SP can be used as a parameterized view or as a rowset source in situations where there is no need to use the rowset directly in a JOIN, UNION, etc. Usually a temporary table will hold the data before it is processed.

  • An SP can return rows in four different situations:

  • From querying a table, view or UDF.

  • From querying the server.

  • As a result of calling an XP

  • By building a SELECT statement with calculated data.



Table querying SPís

Views are very efficient and they should be the preferred sources of rows for all purposes. It would be fine to have twelve views for a particular case, if filtering by a date, one for each month. In some other case, it would be acceptable to have fifty views, one for each state, if that was the filtering column. These are cases where performance has to be maximized but, they are very limited. First of all, the situation with dates would only work if the date column had the same year for all rows. In the situation with filtering by state that is ok but it would be impossible if filtering by city, or any other column that might have a very high number of possibilities.


Example: Create an SP on database Northwind that will return the first name and last name from table employees, filtered by city.


CREATE PROCEDURE SPEmployeeFilterByCity(@city VARCHAR(50))

--returns the employees from a certain city


SELECT FirstName, LastName

FROM  dbo.Employees

WHERE (City = @city)


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