What I am looking to do is use a complicated stored procedure to get
data for me while in another stored procedure.

Its like a view, but a view you can't pass parameters to.

In essence I would like a sproc that would be like this

Create Procedure NewSproc
AS

Select * from MAIN_SPROC 'a','b',.....
WHERE .........


Or Delcare Table @TEMP
@Temp = MAIN_SPROC 'a','b',.....

Any ideas how I could return rows of data from a sproc into another
sproc and then run a WHERE clause on that data?

Thanks
Chris Auer

Instead of using the complicated stored procedure, why don't you use a
user defined function?

Hi

You may be better with a table function, but for a stored procedure you can
create a (temporary) table and use the INSERT...EXEC statement (see Books
online)

e.g.
CREATE TABLE #temporary_table (col1 char(1), col2 char(1) )

INSERT INTO #temporary_table (col1, col2 ) EXEC my_proc

SELECT col1, col2 FROM #temporary_table

John

"Chris Auer" <chris.auer@gmail.com> wrote in message
news:1117740278.709876.4550@o13g2000cwo.googlegrou ps.com...
> What I am looking to do is use a complicated stored procedure to get
> data for me while in another stored procedure.
>
> Its like a view, but a view you can't pass parameters to.
>
> In essence I would like a sproc that would be like this
>
> Create Procedure NewSproc
> AS
>
> Select * from MAIN_SPROC 'a','b',.....
> WHERE .........
>
>
> Or Delcare Table @TEMP
> @Temp = MAIN_SPROC 'a','b',.....
>
> Any ideas how I could return rows of data from a sproc into another
> sproc and then run a WHERE clause on that data?
>
> Thanks
> Chris Auer
>

Sanjeev's right on the money... user defined function will handle this
nicely.

TLetsek


"Chris Auer" <chris.auer@gmail.com> wrote in message
news:1117740278.709876.4550@o13g2000cwo.googlegrou ps.com...
> What I am looking to do is use a complicated stored procedure to get
> data for me while in another stored procedure.
>
> Its like a view, but a view you can't pass parameters to.
>
> In essence I would like a sproc that would be like this
>
> Create Procedure NewSproc
> AS
>
> Select * from MAIN_SPROC 'a','b',.....
> WHERE .........
>
>
> Or Delcare Table @TEMP
> @Temp = MAIN_SPROC 'a','b',.....
>
> Any ideas how I could return rows of data from a sproc into another
> sproc and then run a WHERE clause on that data?
>
> Thanks
> Chris Auer
>

[posted and mailed]

Chris Auer (chris.auer@gmail.com) writes:
> What I am looking to do is use a complicated stored procedure to get
> data for me while in another stored procedure.
>
> Its like a view, but a view you can't pass parameters to.
>
> In essence I would like a sproc that would be like this
>
> Create Procedure NewSproc
> AS
>
> Select * from MAIN_SPROC 'a','b',.....
> WHERE .........
>
>
> Or Delcare Table @TEMP
> @Temp = MAIN_SPROC 'a','b',.....
>
> Any ideas how I could return rows of data from a sproc into another
> sproc and then run a WHERE clause on that data?

There are a couple options, please see an article on my web site:
http://www.sommarskog.se/share_data.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp