Reading a field from Cursor

Hi,
I have a cursor that reads a table. During the reading of the table I want
to read e "field" directly (Not usign FETCH NEXT FROM authors_cursors INTO
@.col1, @.col2).
Thanks!claude81 wrote:
> Hi,
> I have a cursor that reads a table. During the reading of the table I
> want to read e "field" directly (Not usign FETCH NEXT FROM
> authors_cursors INTO @.col1, @.col2).
>
You have to FETCH NEXT INTO. There is no other way.
Cursors should be a last resort.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thaks!!
"Bob Barrows [MVP]" wrote:

> claude81 wrote:
> You have to FETCH NEXT INTO. There is no other way.
> Cursors should be a last resort.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

READ Only Cursor

I have the following cursor decleration that was working before. Each time I
run it now I get the follwoing message:
FOR UPDATE cannot be specified on a READ ONLY cursor.
How do I resolve this?
declare export_cursor cursor for
select [RecordKey]
from [ExportData]
for updateTry,
declare export_cursor cursor
SCROLL_LOCKS
for
select [RecordKey]
from [ExportData]
for update
...
AMB
"Emma" wrote:

> I have the following cursor decleration that was working before. Each time
I
> run it now I get the follwoing message:
> FOR UPDATE cannot be specified on a READ ONLY cursor.
> How do I resolve this?
> declare export_cursor cursor for
> select [RecordKey]
> from [ExportData]
> for update|||I tried the SCROLL_LOCKS and it did not work. The way I had it was working
before. Will recreating the database have anything to do with the cursor not
working?
"Alejandro Mesa" wrote:
> Try,
> declare export_cursor cursor
> SCROLL_LOCKS
> for
> select [RecordKey]
> from [ExportData]
> for update
> ...
>
> AMB
> "Emma" wrote:
>|||Have you considered replacing the cursor with set-based code? Cursors
in general are a bad idea. Update cursors are worse.
--
David Portas
SQL Server MVP
--|||Try,
declare export_cursor cursor
KEYSET
for
select [RecordKey]
from [ExportData]
for update
...
AMB
"Emma" wrote:
> I tried the SCROLL_LOCKS and it did not work. The way I had it was working
> before. Will recreating the database have anything to do with the cursor n
ot
> working?
> "Alejandro Mesa" wrote:
>|||I figured it out. The table has to have a unique index in order for the
update cursor to work. The database was being replicated before and I took
replication off and deleted all the rowid’s added by replication. The rowi
d
was being used as the unique index before.
What is set-based code?
"David Portas" wrote:

> Have you considered replacing the cursor with set-based code? Cursors
> in general are a bad idea. Update cursors are worse.
> --
> David Portas
> SQL Server MVP
> --
>|||Set-based code basically means the standard SELECT, UPDATE, DELETE and
INSERT statements. These operate on sets of rows at a time rather than
individual row-by-row processing.
Set-based SQL is generally much more efficient, more concise and easier to
develop and maintain than cursors. Most of the time cursors are unnecessary
and set-based SQL should be your first choice for performing any data
manipuldation.
David Portas
SQL Server MVP
--

Read Only Cursor

I have a sproc that has been running for over 2 years. We then alter a tabl
e structure
increasince the size of 3 fields, and correspondingly alter an Insert statem
ent for this
table. Now an 'update tablename where current of mycur' much later in the c
ode, issues an error of
'The cursor is Read Only' and fails.
The cursor syntax were :
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
ORDER BY ld_employee_no, adjusted_hours desc
I changed last line to the following and it works.
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
FOR UPDATE OF adjusted_hours
Does anyone have any idea, or should I post more info? We'd really like to
know
why the DDL change and the Insert change would affect a cursor update.
TIA,
Marc MillerWithout seeing the full repro I'm guessing that you changed something
that caused an implicit conversion to a static cursor. Always specify
cursor options explicitly to avoid this.
Could you explain why you are using a cursor at all? Again just
guessing by your code fragment this looks very like a straight data mod
that ought to be possible in an UPDATE with no cursor at all. If this
is a 2 year code legacy then maybe now would be a good time to review
and replace it.
David Portas
SQL Server MVP
--|||David,
I have a table of salaried employee time entires. Reporting requires,
however, that I only
show 40 hours per employee, even though they report overtime hours. Their
time is reported
in quarter hours increments and I need to loop and decrement/increment the
line items by the amount of
the overtime until I can best adjust each line 'evenly' (sort of an
allocation type basis.) to a total of 40 hours
for each person.
I have no idea in the world how I would use an UPDATE to accomplish this.
Thanks,
Marc Miller
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128538402.789619.48450@.z14g2000cwz.googlegroups.com...
> Without seeing the full repro I'm guessing that you changed something
> that caused an implicit conversion to a static cursor. Always specify
> cursor options explicitly to avoid this.
> Could you explain why you are using a cursor at all? Again just
> guessing by your code fragment this looks very like a straight data mod
> that ought to be possible in an UPDATE with no cursor at all. If this
> is a 2 year code legacy then maybe now would be a good time to review
> and replace it.
> --
> David Portas
> SQL Server MVP
> --
>|||> Reporting requires,
> however, that I only
> show 40 hours per employee
If that's just a reporting requirement why do you need to update the
table? Wouldn't it suffice to put the calc in a SELECT statement?

> I have no idea in the world how I would use an UPDATE to accomplish this.
If you want help with that please post DDL, sample data and required
results as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

re: cursor + rounding?

Hi Guys,

I've created a cursor inside a function. When I break it down and execute the code piece by piece, no problem. However, try and parse it together and I get an error - 'Mixing old and new syntax is not allowed?' Something to do with the return statements? as I can alter the function to a procedure and it parses fine. Anyone come across this before?

Also, the values are being rounded when putting them into the cursor, even though I've declared the variables the cursor uses specifically as decimal? How can I get around this please?

Cheers,

Michelle

Michelle:

Can you describe what you are doing? This sounds rather vague.

|||

Blast, it didn't post. Umm, I've sorted the first problem, and I think I may have the answer to the second one. Will give it a go and let you know. Thanks for your help!

Cheers,

Michelle

|||

OK, both of those problems fixed. Should have been

DECLARE product_cursor CURSOR local SCROLL

NOT

DECLARE product_cursor SCROLL CURSOR

and Should have been:

decimal(8,2)

NOT

decimal.

Stupid mistakes! However, now I'm able to parse it fine, but am getting:

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 15

Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 25

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

when I execute it!?

The SELECT INTO and UPDATE statements are fine when executed alone

Michelle