Posted by Simon Barnett on 07/26/07 00:06

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

[Back to original message]