Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-08-11 : 08:35:48
|
| Matthew writes "I have created a stored procedure that returns a record set. I want to call another query that uses the stored procedure as a subquery of my main query but I can't seem to find the right syntax for doing so if it is even possible. My stored procedure is calculating spherical distance from a given zip code and returns the closes contacts via a spherical distance funtion. The stored procedure is working but I can't seem to get it to become part of my larger query.Here is my stored procedure:sp_getcontacts @zipcode='95051'orsp_getcontacts '95051'or exec sProc_vnusPracticeLocateClosest '95051'All of these return my contact index. Now I want to use this dataset something like this:Select * from tblContacts where intContactid in (sp_getcontacts @zipcode='95051')Can you please help me with the correct syntax for using a stored procedures result set as criterium for a larger query?" |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-11 : 09:37:48
|
1. create a #temp table2. insert into #temp exec sp_getcontacts @zipcode='95051'3. select from the #temp table
KH |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-08-11 : 12:33:35
|
| Also make sure that temp table's columns are identical to the datatypes of result of stored procedureMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Stored Procedures as a subquery