how to use SPs output in the SELECT Statement

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,
LuckyHi,

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:

Quote:

Originally Posted by

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

|||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:

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

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 (tushar.n.patel@.gmail.com) writes:

Quote:

Originally Posted by

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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx