how to use SP's output in the SELECT Statement

[Lucky]

hi guys!

it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.

anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :

select * from sp_tables tablename like 'syscolumns'

please note that this is just an example. i'm using different SP but i
want to use in the same way.

if anybody has anything to say. please write to me. i would be glade to
read your replies

Thanks,
Lucky

[Kart]

Hi,

I didnt understand your question completely. But if you mean you want
to use one SP's result in another SP, then there are a few options...

1. Put the value into a temporary table and then access these values in
the second SP.
2. You can have a global cursor

But if you are looking at using one SP's output in a query, then I dont
think this is possible. You can look at creating a table valued
functon.

Regards,
Karthik
Lucky wrote:

hi guys!

it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.

anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :

select * from sp_tables tablename like 'syscolumns'

please note that this is just an example. i'm using different SP but i
want to use in the same way.

if anybody has anything to say. please write to me. i would be glade to
read your replies

Thanks,
Lucky

[Lucky]

Hi Karthik!
yes, i want to use the SP's output in to the query. but if it is not
possible as u said i would like to store it in temporary table as u
said. can u tell me how can i do that?

let's say i want to store output of SP "sp_tables" into temporary
table.
how can i do that?

i would appriciate your help.

thanks,
Lucky

Kart wrote:

Hi,

I didnt understand your question completely. But if you mean you want
to use one SP's result in another SP, then there are a few options...

1. Put the value into a temporary table and then access these values in
the second SP.
2. You can have a global cursor

But if you are looking at using one SP's output in a query, then I dont
think this is possible. You can look at creating a table valued
functon.

Regards,
Karthik
Lucky wrote:

hi guys!

it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.

anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :

select * from sp_tables tablename like 'syscolumns'

please note that this is just an example. i'm using different SP but i
want to use in the same way.

if anybody has anything to say. please write to me. i would be glade to
read your replies

Thanks,
Lucky

[Erland Sommarskog]

Lucky (tu************@gmail.com) writes:

it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.

anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :

select * from sp_tables tablename like 'syscolumns'

please note that this is just an example. i'm using different SP but i
want to use in the same way.

if anybody has anything to say. please write to me. i would be glade to
read your replies

For the precise example of sp_tables, INSERT EXEC is probaly the best way
to go. For procedures you have control over there are better methods. I
happen to have an article on my web site that discusses different options,
http://www.sommarskog.se/share_data.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx