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" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx> wrote in message
news:DeadnUdfnoYDoTXbRVnyhAA@xxxxxxxxxxxxxxx
"Simon Barnett" <sb@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:xARpi.21338$2U6.5520@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
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).aspx
--




.

Relevant Pages

  • Re: Opinions on approach, please...
    ... Code conversion is much more tricky. ... to update more than 32k records without a commit in-between. ... I advise you to do cursor definitions on working storage). ... I don't want to see ANY SQL code in them. ...
    (comp.lang.cobol)
  • Re: Opinions on approach, please...
    ... to update more than 32k records without a commit in-between. ... Translating the above to SQL it would be as follows. ... I advise you to do cursor definitions on working storage). ... exec sql open file-a end-exec. ...
    (comp.lang.cobol)
  • Re: Problem Executing a Stored Proc within a cursor
    ... I will never use Exists again, current versions of SQL server may have ... that you have a strange way of running the cursor loop. ... DECLARE cur CURSOR STATIC LOCAL FOR ...
    (microsoft.public.sqlserver.programming)
  • Re: Millions of Delete Statements
    ... You mention a cursor. ... Others have mentioned batching the commands, ... so I will throw out one that is less good (executing them ... get the whole SQL statement I don't know of another way to do it). ...
    (comp.databases.ms-sqlserver)
  • RE: Problem Executing a Stored Proc within a cursor
    ... that you have a strange way of running the cursor loop. ... DECLARE cur CURSOR STATIC LOCAL FOR ... Always make the cursor STATIC, which means that SQL Server will ...
    (microsoft.public.sqlserver.programming)