passing variable from recordset to recordset

passing variable from recordset to recordset

Post by Glenn Levin » Thu, 01 Feb 2001 07:34:48



Hello, I am working with a stored procedure to return two recordsets to the
application.  The second recordset depends on the first recordset for a
returned value to be used as an input parameter.  I'm having trouble passing
the parameter between the two recordsets without having to go in and out of
the application during the process.


particular country.  Only one record will be returned in all cases.  The
value of the r1.summary will be displayed in the application.  The value of
the r1.mewr_id must be passed along to be used as a parameter in the second
portion of the stored procedure (see STATEMENT 2).

STATEMENT 1
----------------------------------------------------------------------------
-------------
CREATE PROCEDURE [dbo].[get_country_home_2]

as

SELECT
 r1.country_id,
 r1.mewr_id,
 r1.summary

FROM monthly_ew_reports r1 INNER JOIN countries c1 ON r1.country_id =
c1.cy_id

WHERE (r1.cvg_start =
        (SELECT MAX(r2.cvg_start)
      FROM monthly_ew_reports AS r2

----------------------------------------------------------------------------
--------------

In the second recordset, the mewr_id value of the one record returned from
the first statement should be used as the parameter to pass into the this
statement (STATEMENT 2):

STATEMENT 2
----------------------------------------------------------------------------
--------------
SELECT
 r1.mewr_id,
 datename(m, r1.cvg_start) as cvg_start_month,
 datename(d, r1.cvg_start) as cvg_start_day,
 datename(m, r1.cvg_end) as cvg_end_month,
 datename(d, r1.cvg_end) as cvg_end_day

FROM monthly_ew_reports r1 INNER JOIN countries c1 ON r1.country_id =
c1.cy_id

WHERE r1.country_id = '<PARAMETER>'
----------------------------------------------------------------------------
--------------

 How can I write my stored procedure so that in the end, the application
receives two recordsets as described above.

Please help if you can...

___________

 
 
 

passing variable from recordset to recordset

Post by Don Arsenaul » Thu, 01 Feb 2001 10:31:33


CREATE PROCEDURE [dbo].[get_country_home_2]

as




SELECT



FROM monthly_ew_reports r1 INNER JOIN countries c1 ON r1.country_id =
c1.cy_id
WHERE (r1.cvg_start =
        (SELECT MAX(r2.cvg_start)
      FROM monthly_ew_reports AS r2

SELECT



SELECT
 r1.mewr_id,
 datename(m, r1.cvg_start) as cvg_start_month,
 datename(d, r1.cvg_start) as cvg_start_day,
 datename(m, r1.cvg_end) as cvg_end_month,
 datename(d, r1.cvg_end) as cvg_end_day
FROM monthly_ew_reports r1 INNER JOIN countries c1 ON r1.country_id =
c1.cy_id

You have to fill in the "<datatype>" for the above DECLARE's.  You'll have
to use a slightly different method if one of the columns is text, ntext, or
image.

Don


Hello, I am working with a stored procedure to return two recordsets to the
application.  The second recordset depends on the first recordset for a
returned value to be used as an input parameter.  I'm having trouble passing
the parameter between the two recordsets without having to go in and out of
the application during the process.


particular country.  Only one record will be returned in all cases.  The
value of the r1.summary will be displayed in the application.  The value of
the r1.mewr_id must be passed along to be used as a parameter in the second
portion of the stored procedure (see STATEMENT 2).

STATEMENT 1
----------------------------------------------------------------------------
-------------
CREATE PROCEDURE [dbo].[get_country_home_2]

as

SELECT
 r1.country_id,
 r1.mewr_id,
 r1.summary

FROM monthly_ew_reports r1 INNER JOIN countries c1 ON r1.country_id =
c1.cy_id

WHERE (r1.cvg_start =
        (SELECT MAX(r2.cvg_start)
      FROM monthly_ew_reports AS r2

----------------------------------------------------------------------------
--------------

In the second recordset, the mewr_id value of the one record returned from
the first statement should be used as the parameter to pass into the this
statement (STATEMENT 2):

STATEMENT 2
----------------------------------------------------------------------------
--------------
SELECT
 r1.mewr_id,
 datename(m, r1.cvg_start) as cvg_start_month,
 datename(d, r1.cvg_start) as cvg_start_day,
 datename(m, r1.cvg_end) as cvg_end_month,
 datename(d, r1.cvg_end) as cvg_end_day

FROM monthly_ew_reports r1 INNER JOIN countries c1 ON r1.country_id =
c1.cy_id

WHERE r1.country_id = '<PARAMETER>'
----------------------------------------------------------------------------
--------------

 How can I write my stored procedure so that in the end, the application
receives two recordsets as described above.

Please help if you can...

___________


 
 
 

1. Global variable for passing recordset

I'm using SQL 2000 client tools on my desktop, hitting SQL 7 databases
pre-upgrade...

I'm trying to declare a global variable in a DTS package, of type
"other", for holding a recordset which I'll create in an ActiveX task
and use elsewhere in the package.

When I try to declare this global variable as a packge property, I get
a "type mismatch" error, and the variable's type is now "variant". Is
it because of the version difference between the DTS client version
and the server?

The same thing occurs when I declare a global variable from an Execute
SQL task...

TIA
Pam

2. ODBC Timeout error

3. Trying to pass a variable in an ADO recordset query

4. multiple search problem

5. Howto pass NULL variable into some Recordset field?

6. DLL Load error / Connell Book

7. Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

8. Mailmerge from vb using access table and word

9. Passing ADO recordset from Access to SQL

10. pass an array or recordset into a stored proc

11. opening an updatable recordset using a connection passed from an ActiveX EXE

12. ASP Passing back a Recordset to SP

13. Passing recordset to stored procedure or ..