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...
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.
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.
You may link to this article from another website, but ONLY if it is not wrapped in a frame.
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
as
SELECT 'KO' AS Tickersymbol
go
--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
go
--//////////////////////////////////////
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.)
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.)
(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.)
2/19/2002 6:59:05 AM: 
