SP with Select statement

[Gerald]

Hi,

I'm trying to select fileds that are in the results of a SP.

So I have the table "tblItem"

itemID int Identity Key,
itemName varchar (100),
itemDate smalldatetime

I have a SP "p_getItemID"
which is lets say
SELECT TOP 10 itemID FROM tblItem


Now what I want to do is:

SELECT * FROM tblItem WHERE itemID IN (EXEC p_getItemID)

I want to select from the table but only the itemId's returned by the sp.
Can you do that?

Thanks

Gerald

[Roji. P. Thomas]

Execute the second SP and store the values to
a table variable or temp table.

Now your job is easy that all you have to do is

SELECT * FROM tblItem WHERE itemID IN (Select id from temptable)

Or better do an INNER JOIN with the temp table.

--
Roji. P. Thomas
SQL Server Programmer ;)
________________________
"Gerald" <gege107@hotmail.com> wrote in message
news:u0qQXrNtDHA.2360@TK2MSFTNGP09.phx.gbl...

> Hi,
>
> I'm trying to select fileds that are in the results of a SP.
>
> So I have the table "tblItem"
>
> itemID int Identity Key,
> itemName varchar (100),
> itemDate smalldatetime
>
> I have a SP "p_getItemID"
> which is lets say
> SELECT TOP 10 itemID FROM tblItem
>
>
> Now what I want to do is:
>
> SELECT * FROM tblItem WHERE itemID IN (EXEC p_getItemID)
>
> I want to select from the table but only the itemId's returned by the sp.
> Can you do that?
>
> Thanks
>
> Gerald
>
>

[Bob Barrows]

Gerald wrote:

> Hi,
>
> I'm trying to select fileds that are in the results of a SP.
>
> So I have the table "tblItem"
>
> itemID int Identity Key,
> itemName varchar (100),
> itemDate smalldatetime
>
> I have a SP "p_getItemID"
> which is lets say
> SELECT TOP 10 itemID FROM tblItem
>
>
> Now what I want to do is:
>
> SELECT * FROM tblItem WHERE itemID IN (EXEC p_getItemID)
>
> I want to select from the table but only the itemId's returned by the
> sp. Can you do that?
>
> Thanks
>
> Gerald

[url]http://www.algonet.se/~sommar/share_data.html[/url]

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[John Bell]

Hi

You can do this either by changing the stored procedure in to a
function or using a temporary table and the execute_statement option
with the INSERT statement see
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp[/url]

John

"Gerald" <gege107@hotmail.com> wrote in message news:<u0qQXrNtDHA.2360@TK2MSFTNGP09.phx.gbl>...

> Hi,
>
> I'm trying to select fileds that are in the results of a SP.
>
> So I have the table "tblItem"
>
> itemID int Identity Key,
> itemName varchar (100),
> itemDate smalldatetime
>
> I have a SP "p_getItemID"
> which is lets say
> SELECT TOP 10 itemID FROM tblItem
>
>
> Now what I want to do is:
>
> SELECT * FROM tblItem WHERE itemID IN (EXEC p_getItemID)
>
> I want to select from the table but only the itemId's returned by the sp.
> Can you do that?
>
> Thanks
>
> Gerald