Re: Using results form a stored procedure in an exists clause?
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/23/04
- Next message: corben: "Programing DTS in stored proc / transact SQl"
- Previous message: JLS: "Index Question"
- In reply to: Brian Henry: "Using results form a stored procedure in an exists clause?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 23 Apr 2004 10:18:34 -0500
The most straightforward way is to use a temp table:
create table #temp
(
id int
)
insert into #temp
exec sp_getIdNumbers
SELECT Persons.*
from Persons
where Perons.ID exists in (select ID from #temp)
A better way (if this is possible) is to change the sp into a table
function, then it is really simple:
SELECT Persons.*
from Persons
where Perons.ID exists in (select ID from dbo.getIdNumbers())
A couple of nitpicks, it is best not to name your procedures SP_, since that
causes additional work for the query processor (it has to check master first
to see if a procedure with that name exists.) Also, if this is permanent
code (not just testing) it is best not to use the * construct in your
queries. It is best to write out the selected column list. If you don't
need every column, it will be faster.
-- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "Brian Henry" <brianiupmsdn@newsgroups.nospam> wrote in message news:OhiYZrTKEHA.1156@TK2MSFTNGP09.phx.gbl... > How would you go about doing something like using the returned table from > another stored procedure in a select statement to see if something exists in > it > > Say I have stored procedure > SP_GetIDNumbers > which returns a list of ID numbers > > and I want to do this > > SELECT Persons.* from Persons where Perons.ID exists in (stored procedure > results) > > thanks, I dont have any DDL, I'm just running this one out of a question in > my head > >
- Next message: corben: "Programing DTS in stored proc / transact SQl"
- Previous message: JLS: "Index Question"
- In reply to: Brian Henry: "Using results form a stored procedure in an exists clause?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
