Showing posts with label hardfor. Show all posts
Showing posts with label hardfor. Show all posts

Wednesday, March 21, 2012

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