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]

Re: how to use SP's output in the SELECT Statement

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]

Re: how to use SP's output in the SELECT Statement

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]

Re: how to use SP's output in the SELECT Statement

Lucky (tushar.n.patel @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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx