How to pass a recordset to the calling SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to pass a recordset to the calling SP

Author

Topic

awk_grep
Starting Member

11 Posts

Posted - 2007-12-14 : 13:02:14

I have two Stored Procedures. let say sp_a and Sp_b. Sp_a calls Sp_b using the EXEC... my issue is that Sp_B will produce a recordset, like 4 rows and 5 columns...

How can I have the data from sp_b passed back to the calling SP..?
here is some of the code..
-----------------------------
OPEN @cursor_var
set @cnt = 0
set @cnt = @cnt + 1
while (@cnt <= @@cursor_rows)
begin
fetch next from @cursor_var into @emp_loop_id , @daytime
exec SP_B @emp_loop_id,'10/22/2007' ,@emp_id_out output , @total_time_perday output

set @cnt = @cnt + 1
end
--------------------
Once the SP_B is called , the SP_B will create a record set the result from calling SP_B is 4 records or 5 records.

How can I get a record set back from calling SP_B? If I call the SP_B by itself , it will return 4 rows and 5 columns How can I get these results back from calling EXEC SP_B? can it be done?

thank you in advance.
awk_grep@yahoo.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-14 : 13:05:32

you could insert it into a temp table:

create table #t (id int, val int)
insert #t exec myproc -- pass params here if needed.

also see: http://sommarskog.se/share_data.html

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-14 : 14:03:41

I m anot sure if I can access a #TEMP table in SP_B from a calling stored procedure Sp_B...

The Temp table will go away once SP_B is done..

******************************

quote:
Originally posted by jezemine

you could insert it into a temp table:

create table #t (id int, val int)
insert #t exec myproc -- pass params here if needed.

also see: http://sommarskog.se/share_data.html

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-14 : 14:05:49

Sorry I meant to say -- calling Store procedure Sp_a

**********************************

quote:
Originally posted by jezemine

you could insert it into a temp table:

create table #t (id int, val int)
insert #t exec myproc -- pass params here if needed.

also see: http://sommarskog.se/share_data.html

elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-14 : 14:06:10

that depends on how you scope it:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-14 : 17:35:51

if you want spA to have access to the result set of spB, then all you do is this:


create proc spA as
begin
  create table #t (id int, val int)
  insert #t exec spB -- pass params here if needed.
  -- do stuff with #t
end

also you can look at some other methods in the link I posted.

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-14 : 21:47:48

I have tried your solution. It is not working. Do you have a working code? I have done as you suggested, but the #t is empty.. no data..
here is what I have :
OPEN @cursor_var
while (@fetch_cnt <= (select count(*) from #tmpEmpId ))
begin
fetch next from @cursor_var into @emp_loop_id , @daytime
while (@loop_cnt <= @week_days)
begin
--select @emp_loop_id
set @input_date_days = ''
set @input_date_days = convert ( char ,(dateadd (day,@loop_cnt,convert(datetime,@input_date,101))),101)
insert into #tmp_reportdata exec qryPaTimeMainMultiCallRptCSU @emp_loop_id, @input_date_days ,
@id_pk_out output ,
@input_date_out output ,
@emp_id_out output,
@in_out_out output,
@input_time_out output ,
@first_name_out output ,
@last_name_out output ,
@loc_id_out output ,
@total_day_out output,
@total_time_perday_out output

If you see the issue please let me know.. but the #tmp_reportdata is empty.

thank you for your time

*********************

quote:
Originally posted by jezemine

if you want spA to have access to the result set of spB, then all you do is this:

create proc spA as
begin
create table #t (id int, val int)
insert #t exec spB -- pass params here if needed.
-- do stuff with #t
end

also you can look at some other methods in the link I posted.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-14 : 22:39:06

quote:
Originally posted by awk_grep

I have tried your solution. It is not working. Do you have a working code? I have done as you suggested, but the #t is empty.. no data..

probably your proc isn't returning any results for the params you are passing.


create database foo
go

use foo
go

create proc spA as select 1,2 union all select 3,4
go

create proc spB as 
create table #t (a int, b int)
insert #t exec spA
insert #t exec spA
insert #t exec spA
select * from #t
go

exec spB

-- results:
a           b
----------- -----------
1           2
3           4
1           2
3           4
1           2
3           4

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-14 : 23:22:11

I did check my SP and if I take the Insert EXEC from the code and just use the EXEC with output VARs and insert the VARs in to a #t , then I get data... Not the data I need because I am looking for a record set and not that last record..... But never the less I have data... once I add the insert EXEC back into to mix... no data..

What would prevent the data from loading to the #temp table? I have output VARs in me SP, do I need to have outbound VAR's? This is killing me...

***************************

quote:
Originally posted by jezemine

quote:
Originally posted by awk_grep

I have tried your solution. It is not working. Do you have a working code? I have done as you suggested, but the #t is empty.. no data..

probably your proc isn't returning any results for the params you are passing.

create database foo
go

use foo
go

create proc spA as select 1,2 union all select 3,4
go

create proc spB as
create table #t (a int, b int)
insert #t exec spA
insert #t exec spA
insert #t exec spA
select * from #t
go

exec spB

-- results:
a b
----------- -----------
1 2
3 4
1 2
3 4
1 2
3 4

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-14 : 23:32:53

wait a sec. is this for SQL Server, or some other DBMS? some of your syntax doesn't look right. "OPEN @cursor_var" for example.

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-15 : 12:53:21

Yes, this is for SQL/Server 2005...
****************************

quote:
Originally posted by jezemine

wait a sec. is this for SQL Server, or some other DBMS? some of your syntax doesn't look right. "OPEN @cursor_var" for example.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 13:05:50

I see. I didn't even know cursor variables existed in sql server! not sure if I should be embarrassed or proud of that...

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 13:07:38

I don't see where your code is wrong, but you haven't included all of it. it doesn't parse as is. perhaps you are deleting from the temp table somewhere that you are not showing us.

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-15 : 14:28:10

Thank you for your replies.... I owe you one...
Here is the code..
If I take the insert EXEC out and just do the normal insert( is commented out in this code ) it will create results in #tmp_reportdata)... but it will not create any results as is with INSERT_EXEC ..
Thank you for your time...

USE [CPU]
GO
/****** Object: StoredProcedure [dbo].[qryPaTimeMainMultiRptCSU] Script Date: 12/15/2007 13:14:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[qryPaTimeMainMultiRptCSU]
@empid_frm varchar(15),
@empid_thr varchar(15),
@input_date as varchar(20)

AS
BEGIN
SET NOCOUNT ON
declare @input_date_days varchar(20),
@total_time_perday real,
@fetch_cnt smallint,
@loop_cnt smallint,
@emp_loop_id varchar(15),
@week_days smallint,
@daytime datetime,
@rec_cout smallint,
@id_pk_out smallint ,
@emp_id_out varchar(15),
@input_date_out varchar(20),
@in_out_out varchar(2),
@input_time_out varchar(20),
@first_name_out varchar(30),
@last_name_out varchar(50),
@loc_id_out varchar(20),
@total_day_out real,
@total_time_perday_out real ,
@err int,
@cursor_var Cursor
/********************************/
/* Temp Table to hold the EMPID */
/********************************/
CREATE TABLE [#tmpEmpId] (
empid varchar(15) ,
timesheet_day varchar(20))
CREATE TABLE [#tmp_reportdata] (
id_pk_tmp smallint ,
emp_id_tmp varchar(15),
input_date_tmp varchar(20),
in_out_tmp varchar(2),
input_time_tmp varchar(20),
first_name_tmp varchar(30),
last_name_tmp varchar(50),
loc_id_tmp varchar(20),
total_day_tmp real,
total_time_perday_tmp real)
/*******************************/
/* Let's create default values */
/* */
/*******************************/

set @week_days = 6
set @fetch_cnt = 0
set @loop_cnt = 0
set @fetch_cnt = @fetch_cnt + 1
set @input_date_days = ''
set @err = 1
/*******************************/
/* Let's get the EMPIDs ino the*/
/* tmpEmpId */
/*******************************/
begin
insert into #tmpEmpId
(empid,
timesheet_day)
select distinct emp_id, input_date from tblpainoutcsu
where emp_id between @empid_frm and @empid_thr
and input_date = convert(datetime,@input_date,101)
order by emp_id
end
/******************************/
/* Lets create a static cursor*/
/******************************/
begin
set @cursor_var = cursor static for
select * from #tmpEmpId
end
/********************************/
create table #t (id int,val int)
OPEN @cursor_var
while (@fetch_cnt <= (select count(*) from #tmpEmpId ))
begin
fetch next from @cursor_var into @emp_loop_id , @daytime
while (@loop_cnt <= @week_days)
begin
--select @emp_loop_id
set @input_date_days = ''
set @input_date_days = convert ( char ,(dateadd(day,@loop_cnt,convert(datetime,@input_date,101))),101)
insert #tmp_reportdata(
id_pk_tmp ,
emp_id_tmp ,
input_date_tmp ,
in_out_tmp ,
input_time_tmp ,
first_name_tmp ,
last_name_tmp ,
loc_id_tmp ,
total_day_tmp ,
total_time_perday_tmp)
exec qryPaTimeMainMultiCallRptCSU @emp_loop_id, @input_date_days ,
@id_pk_out output ,
@emp_id_out output,
@input_date_out output ,
@in_out_out output,
@input_time_out output ,
@first_name_out output ,
@last_name_out output ,
@loc_id_out output ,
@total_day_out output,
@total_time_perday_out output

/*insert into #tmp_reportdata (
id_pk_tmp ,
input_date_tmp ,
emp_id_tmp ,
in_out_tmp ,
input_time_tmp ,
first_name_tmp ,
last_name_tmp ,
loc_id_tmp ,
total_day_tmp ,
total_time_perday_tmp )
values (
@id_pk_out ,
@input_date_out ,
@emp_id_out ,
@in_out_out ,
@input_time_out ,
@first_name_out ,
@last_name_out ,
@loc_id_out ,
@total_day_out ,
@total_time_perday_out )*/
select * from #tmp_reportdata
set @loop_cnt = @loop_cnt + 1

end
set @loop_cnt = 0
set @fetch_cnt = @fetch_cnt + 1
end

close @cursor_var
deallocate @cursor_var
END

************************

quote:
Originally posted by jezemine

I don't see where your code is wrong, but you haven't included all of it. it doesn't parse as is. perhaps you are deleting from the temp table somewhere that you are not showing us.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 17:23:56

ugh. please use [code ] and [/code ] (without spaces) when pasting large blocks of code.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 17:32:32

does this qrypatimemainmulticallrptcsu proc actually return a result record set? or does it return everything through OUTPUT params?

if it does not return any record set, then that's the reason your temp table is empty. INSERT/EXEC is used to populate a table with the result set returned by a proc, not the OUTPUT params it fills in.

I'm guessing your proc returns no record set.

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-15 : 17:54:28

I have created output VARs in qrypatimemainmulticallrptcsu. this SP returns a SELECT which is using output VARs..I will take the output VARs out and try without outputs and see what happens..

for example,

the qrypatimemainmulticallrptcsu returns this,
select @id_pk_out=id_pk,
@emp_id_out= emliid,
@input_date_out=input_date ,
@in_out_out=in_out,
...
..
So once the outer SP is ran.. the inner sp which is qrypatimemainmulticallrptcsu is doing the work and returning the output values...

please correct me if I am wrong... you are suggesting to take the output VARs from the qrypatimemainmulticallrptcsu and only have it do the select id_pk,
empid,
input_date ,
in_out,
...
..
and this SELCET is returning a recorset which I can push to my #temp table in the calling SP...????

Thank you for time and patients.. thank you.. a Google times

*******************************

quote:
Originally posted by jezemine

does this qrypatimemainmulticallrptcsu proc actually return a result record set? or does it return everything through OUTPUT params?

if it does not return any record set, then that's the reason your temp table is empty. INSERT/EXEC is used to populate a table with the result set returned by a proc, not the OUTPUT params it fills in.

I'm guessing your proc returns no record set.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 18:00:56

i see. so your proc is not like my example at all. you don't need INSERT/EXEC.

you can just do this:


            EXEC qrypatimemainmulticallrptcsu
              @emp_loop_id ,
              @input_date_days ,
              @id_pk_out OUTPUT ,
              @emp_id_out OUTPUT ,
              @input_date_out OUTPUT ,
              @in_out_out OUTPUT ,
              @input_time_out OUTPUT ,
              @first_name_out OUTPUT ,
              @last_name_out OUTPUT ,
              @loc_id_out OUTPUT ,
              @total_day_out OUTPUT ,
              @total_time_perday_out OUTPUT

            INSERT #tmp_reportdata
            select @id_pk_out,
                   @emp_id_out,
                   @input_date_out,
                   @in_out_out,
                   @input_time_out,
                   @first_name_out,
                   @last_name_out,
                   @loc_id_out,
                   @total_day_out,
                   @total_time_perday_out

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-15 : 19:16:40

If I do as you suggested here, It will not return a record set but A record .... so.. where I was expecting 3 records, I would only get the last record... This is the issue which started this message...

I need to have all 3 records to come back to me...

thank you for your time...

***********************

quote:

Originally posted by jezemine

i see. so your proc is not like my example at all. you don't need INSERT/EXEC.

you can just do this:


            EXEC qrypatimemainmulticallrptcsu
              @emp_loop_id ,
              @input_date_days ,
              @id_pk_out OUTPUT ,
              @emp_id_out OUTPUT ,
              @input_date_out OUTPUT ,
              @in_out_out OUTPUT ,
              @input_time_out OUTPUT ,
              @first_name_out OUTPUT ,
              @last_name_out OUTPUT ,
              @loc_id_out OUTPUT ,
              @total_day_out OUTPUT ,
              @total_time_perday_out OUTPUT

            INSERT #tmp_reportdata
            select @id_pk_out,
                   @emp_id_out,
                   @input_date_out,
                   @in_out_out,
                   @input_time_out,
                   @first_name_out,
                   @last_name_out,
                   @loc_id_out,
                   @total_day_out,
                   @total_time_perday_out

elsasoft.org

awk_grep
Starting Member

11 Posts

Posted - 2007-12-15 : 20:13:01

I changed my SP and now I am getting what I was looking for... THe key was to take the outbound VARs from the innner SP and just let the outer SP call it and collect the data....

Thank you for your help... I would not have seen the result , if it was not for your help... Thank you..

keep well and be safe
-kaveh
*************************

quote:

Originally posted by jezemine

i see. so your proc is not like my example at all. you don't need INSERT/EXEC.

you can just do this:


            EXEC qrypatimemainmulticallrptcsu
              @emp_loop_id ,
              @input_date_days ,
              @id_pk_out OUTPUT ,
              @emp_id_out OUTPUT ,
              @input_date_out OUTPUT ,
              @in_out_out OUTPUT ,
              @input_time_out OUTPUT ,
              @first_name_out OUTPUT ,
              @last_name_out OUTPUT ,
              @loc_id_out OUTPUT ,
              @total_day_out OUTPUT ,
              @total_time_perday_out OUTPUT

            INSERT #tmp_reportdata
            select @id_pk_out,
                   @emp_id_out,
                   @input_date_out,
                   @in_out_out,
                   @input_time_out,
                   @first_name_out,
                   @last_name_out,
                   @loc_id_out,
                   @total_day_out,
                   @total_time_perday_out

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 20:52:58

ok. let me end this thread by saying that I am sure there are better ways to do whatever you are trying to do.

calling a proc inside a loop that's nested in a cursor is not efficient. It's highly inefficient actually. but if it satisfies your requirement, fair enough!

elsasoft.org

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources