Cursor operations

From: Andrew Clark (nospam_at_nospam.com)
Date: 12/31/04 

Date: Fri, 31 Dec 2004 17:23:58 GMT

Hello,

Suppose the following:

DECLARE test CURSOR FOR SELECT nameID, firstName FROM testing
DECLARE @array TABLE ( nameID INT NOT NULL,
                                          firstName VARCHAR(32) NOT NULL )

DECLARE arrayCursor CURSOR SCROLL FOR SELECT nameID, firstName from
@array
DECLARE @index INT, @count INT, @offset INT
DECLARE @newID INT, @newName VARCHAR(32)
DECLARE @oldID INT, @oldName VARCHAR(32)

SET @count = (SELECT COUNT(*) FROM testing)
SET @offset = 11

The idea is to use the temporary table as an array to hold the data that
I want to move. I would also like to delete data from the "array" when I
have finished using it so I may insert more and keep the table small.

SET @index = 0
OPEN arrayCursor
FETCH FIRST FROM test INTO @oldID, @oldName
WHILE (@@FETCH_STATUS = 0 AND @index < @count - @offset) BEGIN
    INSERT INTO @array VALUES (@oldID + @offset, @oldName)
    FETCH NEXT FROM arrayCursor INTO @newID, @newName
    DELETE FROM @array WHERE nameID = @newID
    UPDATE testing SET firstName = @newName WHERE nameID = @oldID

    FETCH NEXT FROM test INTO @oldID, @oldName
    SET @i = @i + 1
END

After I grab a row from the "array" I would like to delete it and add the
next row to the array. In that way, I should only have (@offset + 1) rows
in the array at any time. It seems that after the delete, the arrayCursor
is not pointing where I think it should, i.e., the next row on the top (I
guess what I really want is a queue)

Thanks,
Andrew

Relevant Pages

  • Re: NotSupportedException TomTomSDK
    ... The easiest way to marshal this data is as a byte array. ... use the System.Text.Encoding.Unicode.GetString to extract the inline string. ... BitConverter will allow you to get the int members out of the byte ... > Any ideas how to declare the TCHAR? ...
    (microsoft.public.pocketpc.developer)
  • Re: static array initialization in a class
    ... you declare a pointer and use it as an "array". ... For starters, you don't initialise arr. ... Even if those blocks were totally empty before you declared the int*, ...
    (comp.lang.cpp)
  • Re: Warning on assigning a function-returning-a-pointer-to-arrays
    ... declare pfunc as function returning pointer to array 5 of int ... I used cdecl myself to figure out how to declare a function ...
    (comp.lang.c)
  • Re: Ada exception block does NOT work?
    ... Array size is probably the simplest example - you can declare an array inside a procedure whose size is determined by a parameter to that procedure. ... int& m_Count; ... Gods of Heaven, gods of Earth, ...
    (comp.lang.ada)
  • Re: Really tough ADO Stored Procedure Question. Please Help!!!
    ... @lScenarioID_CopyFrom int, ... DECLARE @ErrMSG varchar--This is the max msg size ... ROLLBACK TRANSACTION ... SELECT @lRowCountHolder = MIN ...
    (microsoft.public.sqlserver.odbc)
Quantcast