Getting Data from a storeed procedure in a stored procedure

[Chris Auer]

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]

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

[John Bell]

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" <ch********@gmail.com> wrote in message
news:11********************@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

[TLetsek]

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

TLetsek
"Chris Auer" <ch********@gmail.com> wrote in message
news:11********************@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

[Erland Sommarskog]

[posted and mailed]

Chris Auer (ch********@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, es****@sommarskog.se

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