CODEWITHSUNDEEP
sqlsql-serverstored-procedures
mrN
mrN

Reputation: 3770

Querying a Stored Procedure from a stored procedure

Ok, this might be my another bad question, but still I am hopeful. If you look at my recent question, It was an desperate attempt to simplify the need of nesting a stored procedure inside another.

Basically, I needed to select few rows satisfying a particular condition from a view (view_game), and generate other views(view_pupil_game, view_pupil_doubles, view_game_winner) and combine the outputs to create a another view.

But, looking ahead for alternatives I would like to create a stored procedure, which takes a parameters and gives it to others procedure, and take the output as result set and use it to finalize the main procedure.

Please, help me, give a simple example of treating a stored procedure as a table( or record set) and query it inside a store procedure

Update

To give an idea of what i want, here is a dummy query

begin
Create procedure someProcedure
    @param1 int(3),
    @param2 int(3) 
as
begin
    SELECT * FROM <anotherStoreProcedure @param1, @param2> as set1 
    inner join <anotherStoreProcedure2 @param1, @param2> as set2 on set2.id=set1.id
end
go

Upvotes: 2

Views: 209

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can create table valued UDF's that return some data and then you can use those UDF's in your SP.

Example:

create function SomeFunction1
(
  @param1 int,
  @param2 int
) returns table as 
return
(
  -- Some query that returns something
  select 1 as id,
         @param1 as Col1,
         'SomeFunc1' as Col2
  union all
  select 2, 
         @param2,
         'SomeFunc1' as Col2
)

go

create function SomeFunction2
(
  @param1 int,
  @param2 int
) returns table as 
return
(
  -- Some query that returns something else
  select 1 as id,
         @param1 as Col1,
         'SomeFunc2' as Col2
  union all
  select 2, 
         @param2,
         'SomeFunc2' as Col2
)

go

create procedure SomeProcedure
    @param1 int,
    @param2 int 
as
begin
  -- SP that uses the UDF and joins on column id
  select * 
  from dbo.SomeFunction1(@param1, @param2) as set1 
    inner join SomeFunction2(@param1, @param2) as set2 
      on set2.id=set1.id
end

Use like this:

exec SomeProcedure 3, 4

Result: 

id          Col1        Col2      id          Col1        Col2
----------- ----------- --------- ----------- ----------- ---------
1           3           SomeFunc1 1           3           SomeFunc2
2           4           SomeFunc1 2           4           SomeFunc2

Upvotes: 0

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

Probably you need something like:

-- tables to save result of calling stored procs below
DECLARE @set1 AS TABLE(id INT, value1 varchar(10))
DECLARE @set2 AS TABLE(id INT, value2 varchar(10))

-- save result of dbo.anotherStoreProcedure call into table variable @set1
INSERT INTO @set1 (id, value1)
EXEC dbo.anotherStoreProcedure @param1, @param2

-- save result of dbo.anotherStoreProcedure2 call into table variable @set2
INSERT INTO @set2 (id, value12)
EXEC dbo.anotherStoreProcedure2 @param1, @param2

SELECT 
  * 
FROM 
  set1 
INNER JOIN 
  set2 
ON 
  set2.id = set1.id

Upvotes: 2

Related Questions