Search code examples
sql-servert-sqlcursor

Alternative for this Cursor SQL

How can I do this without the cursor:

SET NOCOUNT ON;
DECLARE @VAR_A BIGINT, @VAR_B TINYINT;

DECLARE _CURSOR CURSOR FOR
SELECT A, B FROM MY_TABLE

OPEN _CURSOR
FETCH NEXT FROM _CURSOR 
INTO @VAR_A, @VAR_B
WHILE @@FETCH_STATUS = 0  
BEGIN
    SELECT TOP 2 A, B, C, ROW_NUMBER() OVER (ORDER BY A DESC) AS ROW_NUM 
    INTO #TMP FROM MY_TABLE_2 
    WHERE A = @VAR_A AND X = 0 ORDER BY A DESC
    IF ((SELECT COUNT(*) FROM #TMP) = 1) BEGIN
        UPDATE MY_TABLE 
          SET Y = (SELECT B FROM #TMP WHERE ROW_NUM = 1) 
        WHERE A = @VAR_A
    END ELSE IF (@VAR_B = 7) BEGIN
        UPDATE MY_TABLE 
        SET Y = (SELECT B FROM #TMP WHERE ROW_NUM = 2), 
            Z = (SELECT C FROM #TMP WHERE ROW_NUM = 2) 
        WHERE A = @VAR_A
    END ELSE BEGIN
        UPDATE MY_TABLE 
        SET Y = (SELECT B FROM #TMP WHERE ROW_NUM = 2) 
        WHERE A = @VAR_A
    END

    DROP TABLE #TMP

    FETCH NEXT FROM _CURSOR   
    INTO @VAR_A, @VAR_B
END   
CLOSE _CURSOR;  
DEALLOCATE _CURSOR;

I have a PRODUCT table, this table has a status column, the last status of the product. I have another table, the PRODUCT_HISTORY, that have all information changed on product, including the older status. For each product, I need to get the last status and insert it on a new field on product table. Like this:

sample

Solution

  • This should do the trick...

    IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL 
DROP TABLE #Product;

CREATE TABLE #Product (
    Id INT NOT NULL PRIMARY KEY CLUSTERED,
    [Description] VARCHAR(20) NOT NULL,
    [Status] INT NOT NULL,
    OlderStatus INT NULL 
    );

INSERT #Product (Id, [Description], [Status]) VALUES 
    (1, 'Product A', 5),
    (2, 'Product B', 7),
    (3, 'Product C', 4),
    (4, 'Product D', 3),
    (5, 'Product E', 0);

IF OBJECT_ID('tempdb..#ProductHistory', 'U') IS NOT NULL 
DROP TABLE #ProductHistory;

CREATE TABLE #ProductHistory (
    Id INT NOT NULL PRIMARY KEY CLUSTERED,
    [Date] DATE NOT NULL,
    Status INT NOT NULL,
    ProductId INT NOT NULL 
    );

INSERT #ProductHistory (Id, [Date], [Status], ProductId) VALUES 
    (1, '2017-01-01', 0, 1),
    (2, '2017-01-02', 1, 1),
    (3, '2017-01-03', 5, 1),
    (4, '2017-01-04', 0, 2),
    (5, '2017-01-05', 1, 2),
    (6, '2017-01-06', 5, 2),
    (7, '2017-01-07', 7, 2),
    (8, '2017-01-08', 0, 3),
    (9, '2017-01-09', 4, 3),
    (10, '2017-01-10', 0, 4),
    (11, '2017-01-11', 3, 4),
    (12, '2017-01-12', 0, 5);

--===================================================

-- the actual solution...
WITH 
    cte_PH AS (
        SELECT 
            ph.Id, ph.Date, ph.Status, ph.ProductId,
            RN = ROW_NUMBER() OVER (PARTITION BY ph.ProductId ORDER BY ph.Date DESC)
        FROM
            #ProductHistory ph
        )
UPDATE p SET
    p.OlderStatus = ISNULL(ph.Status, 0)
FROM
    #Product p
    LEFT JOIN cte_PH ph
        ON p.id = ph.ProductId
        AND ph.RN = 2;

----------------------------------------

SELECT * FROM #Product p;

    Results...

    Id          Description          Status      OlderStatus
----------- -------------------- ----------- -----------
1           Product A            5           1
2           Product B            7           5
3           Product C            4           0
4           Product D            3           0
5           Product E            0           0