Skip to content

Cursor definition and looping with MS SQL

In MSSQL, I needed to loop over a bunch of orders and execute a stored procedure, except in one case. This does that:

declare @orderid int;
DECLARE @RC int
 
declare ordercursor cursor
FOR SELECT id FROM [ORDER];
 
open ordercursor
fetch next FROM ordercursor INTO @orderid
 
while @@fetch_status = 0
begin
  IF @orderid != 429
  begin
    EXECUTE @RC = [DeleteOrderById] @orderid  
  end
  
  fetch next FROM ordercursor INTO @orderid;
end
 
close ordercursor;
deallocate ordercursor;


2 Comments ( Add comment / trackback )

  1. (permalink)
    Comment by Rowan Rodrik
    On October 11, 2013 at 13:30

    Am I not looking right, or is this something that’s much simpler in Postgres?

  2. (permalink)
    Comment by halfgaar
    On October 12, 2013 at 16:35

    Well, it’s been a while since I’ve worked with Postgres, but perhaps there is some automatic thing to execute a stored procedure on a result set of INTs.

    And in any case, this database should just have been configured with proper ON DELETE CASCADE’s, which would have made it possible to simply do a DELETE statement.