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

 

Transferring the output of a SP to a table

If a table needs the output from a SP that returns several rows of output then the INSERT EXEC statement is the perfect choice. It is analogous to INSERT SELECT:

However, INSERT EXEC does not work with variables of type “table”. Trying this technique on such tables will result in the error “EXECUTE cannot be used as a source when inserting into a table variable.”

The only solution is by means of OpenRowSet (opens a new connection though OLE DB) but the downside is the huge performance loss.


Join between a table and the output of a SP This is an example with the sole purpose of proving that it is possible to join the output from a SP with a table. This output, by using OpenRowset, works like a table or view being sorted, filtered, etc. The negative aspect is that OpenRowset opens a new connection to the server as if it was a remote server because that is its functionality. This will result in terrible performance due to the added overhead.


Applying a cursor to the output of a SP

The best solution is to use a temporary table but OpenRowSet will do the job as well. This is the perfect example to demonstrate how OpenRowSet affects performance.


Mean, Median and Modal with SP’s

Sometimes the choice of an algorithm depends on the data source, number of rows, complexity of relationships or joins used in a query, complexity of calculated columns, data type peculiarities, etc. This can be a hard task when there is no real world data for testing, the data is scarce or its changes
unpredictable, the data structure might change, the number of users is unknown, index columns might change, etc. This is what makes database design and development an art. The examples for this section use the Pubs database.
 


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