Important alert: (current site time 5/25/2015 4:44:41 AM EDT)


Join the results of a stored procedure in T-SQL : A Clean Sample

Submitted on: 2/19/2002 4:04:09 AM
By: Harinatha Reddy Gorla 
Level: Intermediate
User Rating: By 8 Users
Compatibility: SQL Server 7.0
Views: 75410
author picture
(About the author)
     There may be several instances where you have to join the results of a stored procedure with another query. Here's how to do it

Terms of Agreement:   
By using this article, you agree to the following terms...   
  1. You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
First, set the data access option for the server: 
exec sp_serveroption '', 'data access', true 
Then, let's create a table called "tblNames" in the "Pubs" datbase: 
TickerSymbol CompanyName 
------------- -------------- 
AMGN Amgen Inc 
KOCoca-Cola Company 
MSFT Microsoft Corporation 
ORCL Oracle Corporation/DE 
PEPPepsiCo Inc 
Use Pubs 
--create a simple stored procedure 
CREATE procedure sp_myproc 
SELECT 'KO' AS Tickersymbol 
--now join the results of SP with query 
select A.CompanyName,A.TickerSymbol from tblNames A 
 join openquery([server_name], '{call pubs..sp_myproc}') B 
 on A.TickerSymbol = B.TickerSymbol 

Other 3 submission(s) by this author


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)

Other User Comments

2/19/2002 6:59:05 AMJames Travis

Awesome, I will need to play with and see what the overhead is.
(If this comment was disrespectful, please report it.)

2/19/2002 7:07:27 AMJames Travis

Oops found a downside. This will not allow you to pass any parameters, you get "[OLE/DB provider returned message: Syntax error or access violation]" as is. Instead of "{call pubs..sp_myproc}" use "exec pubs..sp_myproc" this I have tested will allow you to have a parameter passed.
(If this comment was disrespectful, please report it.)

2/20/2002 6:10:27 AMSreekanth

A Very Nice Explanation Regarding SP
Thanks for the Code too
(If this comment was disrespectful, please report it.)

2/20/2002 6:15:20 AMJames Travis

Ok I finally have the results of the overhead. You will for every individual connection that runs this end up with a secound connection of SQL back to itself with that user which remain open for about 20 minutes or so. However if that user runs an other query doing the same thing the connection will not increase and is fine. After carefull study I cannot find a usefull reason for this as you have to call remotely and unless you build dynamically all your variables are static and in each instance they remain static when built dynamically. For this I would say neat but not practical as there is far less overhead and need to worry about connections with joins. For your example:

select A.CompanyName,A.TickerSymbol from tblNames A
join (SELECT 'KO' AS Tickersymbol) B
on A.TickerSymbol = B.TickerSymbol

Is far better on server resources.
(If this comment was disrespectful, please report it.)

12/3/2003 2:39:24 PM

Numai prostii la SQL!
(If this comment was disrespectful, please report it.)

8/3/2010 7:02:47 AMMindu

Thanks.. Simple its worked for me ..

(If this comment was disrespectful, please report it.)


Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)

To post feedback, first please login.