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

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

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

"Simon Barnett" <sb RemoveThis @simonnospambarnett.com> wrote in message
news:nWRpi.21339$2U6.16614@fe1.news.blueyonder.co.uk...
> 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
>
>
>
>