Assigning current of _Cursor to local @variable

  • Hello,

    I have this nice cursor (yes, I know you should never use them but I feel I must broaden my horizon).

    DECLARE @ColumnE As varchar(50)

    DECLARE product_cursor CURSOR FOR

    SELECT F2 FROM dbo.Products

    FOR UPDATE OF F2

    OPEN product_cursor

    FETCH NEXT FROM product_cursor INTO @ColumnE

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --SET @ColumnE =

    --(SELECT F2

    --FROM products

    --WHERE CURRENT OF product_cursor

    --)

    UPDATE dbo.Products SET F2 = @ColumnE WHERE CURRENT OF product_cursor

    FETCH NEXT FROM product_cursor INTO @ColumnE

    END

    CLOSE product_cursor

    DEALLOCATE product_cursor

    The Issue is, as I have tested with a little "INSERT INTO test VALUES (@ColumnE)", the variable ColumnE always

    seems to be the first row/one that was picked up at the courser declaration i.e.

    the variable is not updated while going through the rows.

    Does anyone have an idea why?

    I built in a variable setter:

    --SET @ColumnE =

    --(SELECT F2

    --FROM products

    --WHERE CURRENT OF product_cursor

    --)

    But it does not work.

    Line 23: CURRENT OF clause allowed only for UPDATE, DELETE.

    Thanks for any help

  • So what is it you are actually trying to accomplish?

    metalray (10/10/2008)


    DECLARE @ColumnE As varchar(50)

    DECLARE product_cursor CURSOR FOR

    SELECT F2 FROM dbo.Products

    FOR UPDATE OF F2

    OPEN product_cursor

    FETCH NEXT FROM product_cursor INTO @ColumnE

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --SET @ColumnE =

    --(SELECT F2

    --FROM products

    --WHERE CURRENT OF product_cursor

    --)

    UPDATE dbo.Products SET F2 = @ColumnE WHERE CURRENT OF product_cursor

    FETCH NEXT FROM product_cursor INTO @ColumnE

    END

    CLOSE product_cursor

    DEALLOCATE product_cursor

    You don't need to try and SELECT a value into @ColumnE, the FETCH does that. I don't really understand your UPDATE statement, it will always be setting the value of column F2 to what it already is. Is there a real problem you were doing this test to try to accomplish? We might be better able to help you if we knew what you were trying to solve.

  • Well, as the error says, CURRENT OF is only allowed for updates and deletes. You use FETCH to get values into variables. Fetch isn't always next. You can get the values for the current cursor row using just FETCH

    FETCH FROM contact_cursor INTO @LastName, @FirstName -- get values for current cursor row

    Is this just for experimentation?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello Old Hand SSCertifiable,

    yes, you are right, the

    UPDATE dbo.Products SET F2 = @ColumnE WHERE CURRENT OF product_cursor

    Is always setting the value of column F2 to what it already is.

    So I need to specifiy the variable setting.

    I think about something like that:

    When:

    ColumnE is not NULL then take that value

    ColumnE is null instert the value taken

    F2 looks like this:

    [F2]

    Car1

    NULL

    NULL

    NULL

    Car2

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    Car3

    NULL

    Car4

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    and I want:

    [F2]

    Car1

    Car1

    Car1

    Car1

    Car2

    Car2

    Car2

    Car2

    Car2

    Car2

    Car2

    Car2

    Car3

    Car3

    Car4

    Car4

    Car4

    Car4

    Car4

    Car4

    Car4

    Car4

    Car4

  • How do you know which "CarN" apply to any particular row with NULL?

    Just in case you don't know, there is no fixed order of rows in relational databases.

    And you cannot rely on "physical order" of rows. It may be changed any moment just because of pages rearrangement or inserting a new row.

    _____________
    Code for TallyGenerator

  • Hi Hall of Fame,

    well I have to rely on the order of rows. The data is imported from

    an excel file.

    I have to use the cursor that goes from top to bottom.

    gets a non null value in to the variable and assigns this

    variable to any column F2 until it hits a non-null value again.

    I think the key is to get the variable to only take

    a value when its not null and assign until it gets the next not null.

  • There's no such thing in SQL as 'Order of rows' unless you specify an order by. The order that the cursor returns them may change from one run to another.

    How are you importing from Excel?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I am using a DTS package. well lets say I put an order by ID (which is from 1 to n ascending) in . that still leaves me with me issue of how to tell the variable what to take (not null) and when to set (null).

  • DECLARE product_cursor CURSOR FOR

    SELECT F2 FROM dbo.Products ORDER BY tempID

    OPEN product_cursor

    --fetches to first row (which is NULL) so it should ideally not be stored in the variable @ColumnE

    FETCH NEXT FROM product_cursor INTO @ColumnE

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- This is executed as long as the previous fetch succeeds.

    FETCH FROM product_cursor INTO @ColumnE WHERE product_curser IS NOT NULL

    --I am trying to fetch only the NOT NULL rows into the variable @ColumnE

    --PROBLEM!!!!!!!!!! a WHERE clause *conditional fetch* is not allowed at this point "Incorrect syntax --near the keyword 'WHERE'."

    --fetches another row (which is probably NULL) so it should ideally not be stored in the variable @ColumnE

    UPDATE dbo.Products SET F2 = @ColumnE WHERE F2 IS NULL

    -- I am trying to set F2, in case it is NULL, to the current non-null value fetched into varialbe @ColumnE

    END

    --CLOSE product_cursor

    --DEALLOCATE product_cursor

  • You don't need to do another fetch in the middle of the cursor loop. Fetch can't have a condition on it. It fetches values from a cursor row. All you can do is say this row, next row or a few other offset options

    This will fix the cursor. I'm sure there's a solution that doesn't use cursors, I can't think of one off the top of my head though.

    DECLARE @OldValue VARCHAR(10), @F2 VARCHAR(10), @ID int

    DECLARE product_cursor CURSOR LOCAL STATIC READ_ONLY_FORWARD_ONLY FOR

    SELECT tempID, F2 FROM dbo.Products ORDER BY tempID

    OPEN product_cursor

    FETCH NEXT FROM product_cursor INTO @id, @F2

    SET @OldValue = @F2 -- assumes the first row will always have a value

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @F2 IS NULL

    UPDATE dbo.Products SET F2 = @OldValue WHERE TempID = @id

    ELSE

    SET @OldValue = @F2

    FETCH NEXT FROM product_cursor INTO @id, @F2

    END

    CLOSE product_cursor

    DEALLOCATE product_cursor

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi SSCertifiable,

    thanks for the code.

    THe issue is that somehow that my table looks like this:

    [F2]

    Car1

    Car1

    Car1

    Car1

    Car2

    Car1

    Car1

    Car1

    Car1

    Car1

    Car1

    Car1

    Car3

    Car1

    Car4

    Car1

    Car1

    Car1

    Car1

    Car1

    Car1

    Car1

    Car1

    it seems like the cursor has fetched "Car1" and inserted it wherever there is a NULL

    value without updating and using F2 if there is a non-null value such as Car2, Car3 or Car4.

    You also wrote -- assumes the first row will always have a value, actually the first few rows (column F2)

    are NULL (but I need them because the other columns have information in them).

  • that fixed it "AND @OldValue IS NOT NULL"

    DECLARE @OldValue VARCHAR(50), @F2 VARCHAR(50), @ID int

    DECLARE product_cursor CURSOR LOCAL

    FOR

    SELECT tempID, F2 FROM dbo.Products ORDER BY tempID

    OPEN product_cursor

    FETCH NEXT FROM product_cursor INTO @ID, @F2

    SET @OldValue = @F2

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @F2 IS NULL

    UPDATE dbo.Products SET F2 = @OldValue WHERE TempID = @ID AND @OldValue IS NOT NULL

    ELSE

    SET @OldValue = @F2

    FETCH NEXT FROM product_cursor INTO @ID, @F2

    END

    Thanks a lot !

  • Yes, your previous code, if you ran it would have done that, as your update statement was

    UPDATE dbo.Products SET F2 = @ColumnE WHERE F2 IS NULL

    If you ran that at all, then all of the table where F2 was null would have been updated with the first non-null value of @ColumnE. I've tested my code and it does what you want.

    Can you reimport the table from Excel and start from scratch?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Create table Products (

    tempid int identity,

    F2 varchar(10)

    )

    INSERT INTO Products (F2) VALUES ('Car1')

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES ('Car2')

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES ('Car3')

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES ('Car4')

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    INSERT INTO Products (F2) VALUES (NULL)

    select * from Products

    DECLARE @OldValue VARCHAR(10), @F2 VARCHAR(10), @ID int

    DECLARE product_cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR

    SELECT tempID, F2 FROM dbo.Products ORDER BY tempID

    OPEN product_cursor

    FETCH NEXT FROM product_cursor INTO @id, @F2

    SET @OldValue = @F2 -- assumes the first row will always have a value

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @F2 IS NULL

    UPDATE dbo.Products SET F2 = @OldValue WHERE TempID = @id

    ELSE

    SET @OldValue = @F2

    FETCH NEXT FROM product_cursor INTO @id, @F2

    END

    CLOSE product_cursor

    DEALLOCATE product_cursor

    -- drop table Products

    Results:

    tempidF2

    1Car1

    2Car1

    3Car1

    4Car1

    5Car2

    6Car2

    7Car2

    8Car2

    9Car2

    10Car2

    11Car2

    12Car2

    13Car3

    14Car3

    15Car4

    16Car4

    17Car4

    18Car4

    19Car4

    20Car4

    21Car4

    22Car4

    23Car4

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I wouldn't feel right if I didn't offer a non-cursor alternative.

    Below should work fine for your scenario, but you may need to tweak your ProductId datatype in the @temp table and the @Prod Id variable.

    DECLARE @temp TABLE

    (

    tmp_Id INT IDENTITY,

    ProductId INT,

    ColumnE VARCHAR(50)

    )

    INSERT INTO @temp

    SELECT ProductId,F2 FROM Products

    DECLARE @Count INT

    DECLARE @Location INT

    DECLARE @PrevF2 VARCHAR(50)

    SELECT @Count = COUNT(1) FROM @temp

    SET @Location = 1

    WHILE @Location <= @Count

    BEGIN

    DECLARE @ColumnEVARCHAR(50)

    DECLARE @ProdID INT

    SELECT @ColumnE = ISNULL(ColumnE,@PrevF2), @ProdId = ProductId

    FROM @temp

    WHERE tmp_Id = @Location

    UPDATE Products

    SET F2 = @ColumnE

    WHERE ProductId = @ProdId

    SET @PrevF2 = @ColumnE

    SET @Location = @Location + 1

    END

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply