Join the results of a stored procedure in T-SQL

There may be instances where you have to join the results of a stored procedure with another query. Here's how to do it.

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.

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 
KO              Coca-Cola Company 
MSFT            Microsoft Corporation 
ORCL            Oracle Corporation/DE 
PEP             PepsiCo 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 

--////////////////////////////////////// 

For More Information

  • What do you think about this tip? E-mail us at tdichiara@techtarget.com with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in November 2001

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close