nested stored procedure(sp2) within a stored procedure1(sp1) to use the output of sp2 as an input to sp2

Hi experts,
I have to create a stored procedure which has a nested stored procedure within, the structure should look like:

CREATE PROCEDURE SP1
      @params            int,
      
AS
 
      
      BEGIN
            SELECT col1, col2 ... coln
            FROM yourTable
            WHERE somecolumn = @params;
      END;
 
        
 
CREATE PROCEDURE SP2
      select c1,c2 from table

The output column c2 of of SP2 should be the input @params of SP1,
should I write a cursor to achieve this or should it be a nested procedure? please help me with the sql for this.

The @params will get multiple values and which are passed through the SP1 to achieve results, not sure if I am clear, please let me know of any questions.
Thanks!
shyamaladevibAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AmmarRCommented:
hi shyamaladevib

whats your goal

if all you want is the output of SP1

you can do the following

SELECT col1, col2 ... coln
            FROM yourTable
            WHERE somecolumn in (select c2 from table)

---

i mean this script above will get you all the records from yourTable that Somecolumn is in c2 of table.

or if you want to can convert your SP1 into a function and call it in a select statment of sp2

like

select c1,c2, dbo.SP1(c2) from table

this will send c2 to the function in every record and but will retrieve a table from the SP1.

i guess if you define your output clearly, i will be able to help you better.

regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
Hi

-- in spOutter
-- create a temp table
create table #TempTable(
  col1 int
  , col2 varchar( 20 )

insert #TempTable
exec spInner @Parameter1 = SomeValue
;

Notes - have to get the columns and types to match output from spInner. Obviousyly can't have multiple outputs from spInner.

In SQL2008 think about using TableValued functions and TableVariables instead of this old way.

For instance,

select *
from function_was_spInner( SomeValue )
;

Regards
  David
0
shyamaladevibAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.