SQL Server Loop without Cursor

Question

I had to create test data that updated the date column of of 2000 records, with 10 rows of the same the date, decrementing the date by 1 on the 11th row.

Here is the T-SQL:

DECLARE @CNT INT = 0;
DECLARE @DAYSUBTRACT INT = 0;
DECLARE @ACCOUNT_ID INT;

DECLARE Update_Cursor CURSOR FOR
SELECT TOP 2000 ACCOUNT_ID 
FROM ACCOUNTS;

OPEN Update_Cursor;
FETCH NEXT FROM Update_Cursor INTO @ACCOUNT_ID;
WHILE @@FETCH_STATUS = 0
   BEGIN
      UPDATE ACCOUNTS
      SET DATE_OPENED = DATEADD(day,@DAYSUBTRACT,SYSDATETIME())
      WHERE ACCOUNT_ID = @ACCOUNT_ID

      SET @CNT = @CNT + 1;

      IF (@CNT%10 = 0) SET @DAYSUBTRACT = @DAYSUBTRACT - 1;

      FETCH NEXT FROM Update_Cursor INTO @ACCOUNT_ID;
   END;
CLOSE Update_Cursor;
DEALLOCATE Update_Cursor;
GO

Is there away to accomplish this without using a cursor in T-SQL?

You are using TOP without ORDER BY, so your results are indeterminate. — Gordon Linoff
– Gordon Linoff, Commented Nov 7, 2015 at 0:28
Good catch, I accidentally deleted the order by when I simplified the example. — Josh
– Josh, Commented Nov 7, 2015 at 0:41

Gordon Linoff · Accepted Answer · 2015-11-07 00:31:14Z

4

The logic is a little hard to follow, but it seems that you want to subtract 1 for the first 10 rows, then 2 for the next 10 and so on. You can do this without a loop:

with toupdate as (
      SELECT TOP 2000 ACCOUNT_ID,
             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as seqnum
      FROM ACCOUNTS
     )
update toupdate
    set date_opened = dateadd(day, - (1 + (seqnum - 1) / 10), SYSDATETIME());

As mentioned in a comment: the results are indeterminate because the TOP is being used without an ORDER BY.

answered Nov 7, 2015 at 0:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shawnt00 · Accepted Answer · 2015-11-07 00:44:15Z

Gordon's approach is certainly superior; nevertheless, it is still possible to convert your approach to one that doesn't require a cursor. Whether this is a better way is up to you:

DECLARE @CNT INT = 0;
DECLARE @DAYSUBTRACT INT = 0;
DECLARE @ACCOUNT_ID INT;

CREATE TABLE ##ACCOUNTS (ACCOUNT_ID INT NOT NULL);
INSERT INTO ##ACCOUNTS (ACCOUNT_ID)
SELECT TOP 2000 ACCOUNT_ID 
FROM ACCOUNTS;

SET @ACCOUNT_ID = (SELECT MIN(ACCOUNT_ID) FROM ##ACCOUNTS);
WHILE @ACCOUNT_ID IS NOT NULL
BEGIN
    UPDATE ACCOUNTS
    SET DATE_OPENED = DATEADD(day, @DAYSUBTRACT, SYSDATETIME())
    WHERE ACCOUNT_ID = @ACCOUNT_ID;

    SET @CNT = @CNT + 1;
    IF @CNT % 10 = 0 SET @DAYSUBTRACT = @DAYSUBTRACT - 1;

    DELETE FROM ##ACCOUNTS WHERE ACCOUNT_ID = @ACCOUNT_ID;
    SET @ACCOUNT_ID = (SELECT MIN(ACCOUNT_ID) FROM ##ACCOUNTS);
END

Collectives™ on Stack Overflow

SQL Server Loop without Cursor

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related