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

 

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

AS 

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.

 

CREATE PROCEDURE ServicePack

AS

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

SELECT CASE SUBSTRING(@@Version,30,9)

      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.

 

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