Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: SQL HELP PLEASE!! Cursor only returns part of the data

Posted on 2007-07-26 02:30:11 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 @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

Report this message

#2: Re: SQL HELP PLEASE!! Cursor only returns part of the data

Posted on 2007-07-26 02:40:22 by 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

"Simon Barnett" <sb@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
>
>
>
>

Report this message