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

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

Simon Barnett wrote:

> 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.

Why is a cursor being used? Have you tried rewriting it
without one?

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

"Simon Barnett" wrote in message
news:xARpi.21338$2U6.5520@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.
>

Don't use a cursor. If you are inexperienced with SQL then it's better not
to use cursors at all. Instead, always try for a single query solution. Find
some examples to expand your knowledge of set-based SQL or get some advice
and assistance.

Here's my guess of what you intended. It's untested. If you had posted DDL
and sample data I could have tested it out.

SELECT KeyAccountability AS col1,
'keyacc' AS rowtype
FROM KeyAccountability
WHERE category = @var1
AND jobprofileid = @jobprofileID
UNION
SELECT category AS col1,
'cat' AS rowtype
FROM KeyAccountability
WHERE category = @var1
AND jobprofileid = @jobprofileID;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).as px
--

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

David,

I'm pretty basic with sql and thought that I needed a cursor but your
suggestion has worked.

Are cursors deemed as bad practice? Everyone I have spoken to says to
avoid them.

Many thanks
Simon

"David Portas" wrote in message
news:DeadnUdfnoYDoTXbRVnyhAA@giganews.com...
> "Simon Barnett" wrote in message
> news:xARpi.21338$2U6.5520@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.
>>
>
> Don't use a cursor. If you are inexperienced with SQL then it's better not
> to use cursors at all. Instead, always try for a single query solution.
> Find some examples to expand your knowledge of set-based SQL or get some
> advice and assistance.
>
> Here's my guess of what you intended. It's untested. If you had posted DDL
> and sample data I could have tested it out.
>
> SELECT KeyAccountability AS col1,
> 'keyacc' AS rowtype
> FROM KeyAccountability
> WHERE category = @var1
> AND jobprofileid = @jobprofileID
> UNION
> SELECT category AS col1,
> 'cat' AS rowtype
> FROM KeyAccountability
> WHERE category = @var1
> AND jobprofileid = @jobprofileID;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).as px
> --
>
>

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

Ed

I thought that a cursor was the only way to do it but David (other post) has
steered me right.

Thanks
Simon

"Ed Murphy" wrote in message
news:46a81d6c$0$12249$4c368faf@roadrunner.com...
> Simon Barnett wrote:
>
>> 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.
>
> Why is a cursor being used? Have you tried rewriting it
> without one?

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

Simon Barnett (sb@simonnospambarnett.com) writes:
> Are cursors deemed as bad practice? Everyone I have spoken to says to
> avoid them.

It's better to say: they are rarely the right tool for the task. But
sometimes they are.

First of all, it's not the cursor as such that is bad, it is the looping.
I say this, so that next time you have to develop solution that requires
looping, you don't go and implement the loop without a cursor. That is
usually even worse.

So when is looping and cursors the right thing:

1) There is a stored procedure that accepts scalar input that you need
to call, and re-writing it to handle set-based data will cost you more
than the performance degradation. That is, the stored procedure is
very complex, or a system procedure, like xp_smtpmail.

2) You process data from some untrusted source, and if there are errors
in the data, you don't want the entire processing to fail, just the
bad rows. Doing this set-based, requiring doubling all constraints,
trigger checks, so you may be prepared to take the performance hit.

3) When they give better performance. The reason cursors in the very
most cases are a poor solution is that the performance is magnitudes
worse than a set-based solution. But there are operations where a
set-based solution do not perform well, often because the language
lacks a way of expressing them. Two such examples are numbering rows
in the output, or include a running sum in the result set. Doing this
with set-based in the proper way in SQL 2000 for a large set of data,
gives outrageous performance, which a cursor easily beats. SQL 2005 adds
new syntax that makes the row-numbering affair very quick. But the
syntax for running sums is still missing, although it's defined by
ANSI.

And possibly 4) The logic is very complicated, and it can be easier to
express it in traditional programming terms, even for a seasoned SQL
developer, and the reduced development cost outweighs the performance
cost for the cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx