SQL HELP PLEASE!! Cursor only returns part of the data

[Simon Barnett]

Hi,



I would much appreciate some help with a work project that is due very soon.



I have used a cursor to return the required result from a db table in order
for me to use in an ASP/VBScript webpage. This is the first time I have
used a cursor and am having problems.



The problem is that instead of returning all the data as a single query
result that can be loaded into an ADO recordset and looped thru in my ASP
page, it is returned (when run in Query Analyser) as 3 individual query
results in 3 different frames (the same as if you ran 3 individual queries
in the QA window at the same time) - one for each of the rows that make up
the cursor. So when I loop through the recordset on my webpage it only
contains one of the query results and not all of the data I require.



Below is my representation of a chunk of the db table, the tsql as run in
MSSQL Query Analyser and a representation of the results returned.



I hope I have made this email clear enough, let me know if otherwise. Many
thanks in advance for your help.

Simon Barnett



Table

ID_col, Category_col, KeyAccountability_col,
PerformanceMeasure_col, StaffID_col
1, Delivery, KeyAcc1,
PerfMeas1, 3
3, Delivery, KeyAcc2,
PerfMeas2, 3
7, Delivery, KeyAcc3,
PerfMeas3, 3
8, Department, KeyAcc4,
PerfMeas4, 3
11, Department, KeyAcc5, PerfMeas5,
3
12, Department, KeyAcc6, PerfMeas6,
3
13, Communications, KeyAcc7, PerfMeas7,
3
16, Communications, KeyAcc8, PerfMeas8,
3

Stored Procedure

declare @var0 nchar(56)
declare @var1 nchar(56)
declare keyaccscursor cursor for
(SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid =
@jobprofileID)
OPEN keyaccscursor
FETCH NEXT FROM keyaccscursor
INTO @var1
WHILE @@FETCH_STATUS = 0
BEGIN
select distinct KeyAccountability as col1, 'keyacc' as rowtype from
KeyAccountability where (category = @var1) and (jobprofileid =
@jobprofileID)
union
select distinct category as col1, 'cat' as rowtype from
KeyAccountability where (category = @var1) and (jobprofileid =
@jobprofileID)
FETCH NEXT FROM keyaccscursor
INTO @var1
END
CLOSE keyaccscursor
DEALLOCATE keyaccscursor

Results (when run in MSSQL Query Analyser )
-------------------------------------------------------------------------------------------------
KeyAccountability PerformanceMeasure (query result column
headings)
Delivery
KeyAcc1 PerfMeas1
KeyAcc2 PerfMeas2
KeyAcc3 PerfMeas3
-------------------------------------------------------------------------------------------------
KeyAccountability PerformanceMeasure (query result column
headings)
Department
KeyAcc4 PerfMeas3
KeyAcc5 PerfMeas4
KeyAcc6 PerfMeas5
-------------------------------------------------------------------------------------------------
KeyAccountability PerformanceMeasure (query result column
headings)
Communications
KeyAcc7 PerfMeas6
KeyAcc7 PerfMeas7

[Simon Barnett]

Apologies - I only just noticed that this is a mySQL group not MSSQL.

Although I've always been led to believe that open source developers spit on
sn MS clones, if you can help with my problem I would be very grateful.

Many thanks
Simon

Post by Simon Barnett
Hi,
I would much appreciate some help with a work project that is due very soon.
I have used a cursor to return the required result from a db table in
order for me to use in an ASP/VBScript webpage. This is the first time I
have used a cursor and am having problems.
The problem is that instead of returning all the data as a single query
result that can be loaded into an ADO recordset and looped thru in my ASP
page, it is returned (when run in Query Analyser) as 3 individual query
results in 3 different frames (the same as if you ran 3 individual queries
in the QA window at the same time) - one for each of the rows that make up
the cursor. So when I loop through the recordset on my webpage it only
contains one of the query results and not all of the data I require.
Below is my representation of a chunk of the db table, the tsql as run in
MSSQL Query Analyser and a representation of the results returned.
I hope I have made this email clear enough, let me know if otherwise.
Many thanks in advance for your help.
Simon Barnett
Table
ID_col, Category_col, KeyAccountability_col,
PerformanceMeasure_col, StaffID_col
1, Delivery, KeyAcc1, PerfMeas1,
3
3, Delivery, KeyAcc2, PerfMeas2,
3
7, Delivery, KeyAcc3, PerfMeas3,
3
8, Department, KeyAcc4, PerfMeas4,
3
11, Department, KeyAcc5,
PerfMeas5, 3
12, Department, KeyAcc6,
PerfMeas6, 3
13, Communications, KeyAcc7, PerfMeas7,
3
16, Communications, KeyAcc8, PerfMeas8,
3
Stored Procedure
declare keyaccscursor cursor for
(SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid =
@jobprofileID)
OPEN keyaccscursor
FETCH NEXT FROM keyaccscursor
BEGIN
select distinct KeyAccountability as col1, 'keyacc' as rowtype from
@jobprofileID)
union
select distinct category as col1, 'cat' as rowtype from
@jobprofileID)
FETCH NEXT FROM keyaccscursor
END
CLOSE keyaccscursor
DEALLOCATE keyaccscursor
Results (when run in MSSQL Query Analyser )
-------------------------------------------------------------------------------------------------
KeyAccountability PerformanceMeasure (query result column
headings)
Delivery
KeyAcc1 PerfMeas1
KeyAcc2 PerfMeas2
KeyAcc3 PerfMeas3
-------------------------------------------------------------------------------------------------
KeyAccountability PerformanceMeasure (query result column
headings)
Department
KeyAcc4 PerfMeas3
KeyAcc5 PerfMeas4
KeyAcc6 PerfMeas5
-------------------------------------------------------------------------------------------------
KeyAccountability PerformanceMeasure (query result column
headings)
Communications
KeyAcc7 PerfMeas6
KeyAcc7 PerfMeas7