Stored Proc output to be used in select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Proc output to be used in select

Author

Topic

anand_d
Starting Member

9 Posts

Posted - 2006-07-03 : 06:09:48

Hi, My requirement is to use the output of the stored procedure in a select statement so that I can get selective rows/cols. Find an example below:
------ This is my proc stmt ------
create proc prcTest
as
begin
select * from pivot
end

--- I want to use something like this -----

select year from (exec prctest)a where year=1990

--- Bcoz I can use the stmt below which is valid -------

select year from (select * from pivot)a where year=1990

Would like the experts to review this and let me know a solution/workaround to achieve this.

~Anand

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 06:17:08

Can you use VIEW ?


create view vTest
as
select <column name> from pivot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 06:18:10

Instead of

select year from (exec prctest)a where year=1990

use

Create table #t(...) --same structure as that of sp results

Inset into #t
EXEC prctest

Select * from #t

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-03 : 06:19:01

Rather than using SP, UDF seems more suitable solution for your problem. Try an inline functions as shown below:

create function somename()
returns table
as
return(select * from pivot)
go
and then you can use following statement to fetch the data:

select * from somename()

Harsh Athalye
India.
"Nothing is Impossible"

anand_d
Starting Member

9 Posts

Posted - 2006-07-03 : 09:58:30

All - Thanks.

But my requirement is slightly different here. I am using one of the production server where in I have read access. Now, there are lot of stored procs which I reqd to understand and from those procs I would require to filter records/filter columns/get distinct value of one particular column.

The soln given here are good enough where I have complete access of database. But here, I would I just have read access.

Hope I have explained my requirements clearly.

~Anand

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 10:03:51

quote:
Originally posted by anand_d

All - Thanks.

But my requirement is slightly different here. I am using one of the production server where in I have read access. Now, there are lot of stored procs which I reqd to understand and from those procs I would require to filter records/filter columns/get distinct value of one particular column.

The soln given here are good enough where I have complete access of database. But here, I would I just have read access.

Hope I have explained my requirements clearly.

~Anand

In that case, Madhivanan's method might be most suitable for you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources