User Tools

Site Tools


notes:sql:cursors

SQL Cursors

Simple static cursors:

-- Example #1
DECLARE @BookId INT
DECLARE cur CURSOR FOR SELECT BookId FROM Book
OPEN cur FETCH NEXT FROM cur INTO @BookId
 
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @BookId
 
    -- If we needed to end the loop conditionally    
    /*
    IF ...
    BEGIN
        FETCH NEXT FROM cur INTO @BookId
        CONTINUE
    END
    */
 
    FETCH NEXT FROM cur INTO @BookId
END
 
CLOSE cur
DEALLOCATE cur
 
 
-- Example #2
DECLARE @FlagID INT, @MultipleFlagID VARCHAR(2000)
DECLARE cur CURSOR FOR SELECT FlagID FROM Flags WHERE MessageID=@MessageID
OPEN cur FETCH NEXT FROM cur INTO @FlagID
 
WHILE @@FETCH_STATUS=0
BEGIN
    SET @MultipleFlagID = @MultipleFlagID + CAST(@FlagID AS VARCHAR(10))+','
    FETCH NEXT FROM cur INTO @FlagID
END
 
CLOSE cur
DEALLOCATE cur
 
 
-- Example #3
DECLARE @Price MONEY, @TYPE INT, @DESC VARCHAR(1000)
SET @DESC = ''
DECLARE cur CURSOR FOR SELECT ItemPrice, ItemType FROM Item
 
OPEN cur FETCH NEXT FROM cur INTO @Price, @TYPE
 
WHILE @@FETCH_STATUS=0
BEGIN
    SET @DESC = @DESC + 
        CASE WHEN @TYPE=0 THEN '-' ELSE '+' END +
        CASE WHEN @Price IS NULL THEN '' 
             WHEN @Price=0 THEN '($0.00) '
             ELSE '($' + CONVERT(VARCHAR, CAST(@Price AS MONEY), 1) + ') ' END
    FETCH NEXT FROM cur INTO @Price, @TYPE
END
 
CLOSE cur
DEALLOCATE cur

Updatable cursors:

-- Update all books of type BookType='A' to have rating 1
DECLARE @BookId INT, @Rating INT
DECLARE cur CURSOR FOR SELECT BookId FROM Book WHERE BookType='A' FOR UPDATE OF Rating
OPEN cur FETCH NEXT FROM cur INTO @BookId
 
WHILE @@FETCH_STATUS=0
BEGIN
    DECLARE @Rating INT
 
    -- Calculate @Rating
    -- ...
 
    UPDATE Book SET Rating=@Rating WHERE CURRENT OF cur
 
    FETCH NEXT FROM cur INTO @BookId
END
 
CLOSE cur
DEALLOCATE cur

Nested cursors:

DECLARE @Title VARCHAR(100), @BookType CHAR, @AllTitles VARCHAR(2000)
 
-- outer cursor
DECLARE outcur CURSOR FOR SELECT DISTINCT BookType FROM Book
OPEN outcur FETCH NEXT FROM outcur INTO @BookType
 
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @BookType
    SET @AllTitles = ''
 
    -- inner cursor
    DECLARE inn CURSOR FOR SELECT Title FROM Book WHERE BookType=@BookType
    OPEN inn FETCH NEXT FROM inn INTO @Title
 
    WHILE @@FETCH_STATUS=0
    BEGIN
        SET @AllTitles = @AllTitles + @Title + ', '
        FETCH NEXT FROM inn INTO @Title
    END
 
    CLOSE inn
    DEALLOCATE inn
 
    -- Eat the last space after the comma
    IF LEN(@AllTitles) >= 1 SET @AllTitles = SUBSTRING(@AllTitles, 1, LEN(@AllTitles)-1)
 
    PRINT @AllTitles
 
    FETCH NEXT FROM outcur INTO @BookType
END
 
CLOSE outcur 
DEALLOCATE outcur 

A fast, forward-only cursor:

DECLARE @MaxNumber INT
 
-- Get the max TicketNo
SELECT @MaxNumber = MAX(TicketNo) FROM Ticket
 
DECLARE cur CURSOR FORWARD_ONLY FOR
    SELECT TicketNo FROM Ticket
    WHERE TicketNo=0
    FOR UPDATE OF TicketNo
 
OPEN cur FETCH NEXT FROM cur
WHILE @@FETCH_STATUS != -1
BEGIN
    SET @MaxNumber=@MaxNumber+1
 
    -- Assign a new TicketNo if the ticket does not have a number    
    UPDATE Tickets SET TicketNo=@MaxNumber
    WHERE CURRENT OF cur
 
    FETCH NEXT FROM cur
END
DEALLOCATE cur

Transact-SQL Extended Syntax (from MSDN):

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
 
-- Example
DECLARE cur CURSOR
GLOBAL          -- We can manipulate the cursor outside the batch
SCROLL          -- We can scroll back to see if the changes are there
KEYSET
TYPE_WARNING    -- We will be notified if an implicit conversion is made on the cursor
                -- Without a unique index on a table the cursor will be converted from KEYSET to DYNAMIC
FOR SELECT OrderID, CustomerID FROM MyTable
notes/sql/cursors.txt · Last modified: 2020/08/26 (external edit)