Solved

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

Posted on 2010-11-30
3
420 Views
Last Modified: 2012-05-10
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!
0
Comment
Question by:shyamaladevib
3 Comments
 
LVL 15

Accepted Solution

by:
AmmarR earned 250 total points
ID: 34246683
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 34278188
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
 

Author Closing Comment

by:shyamaladevib
ID: 34293007
Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question