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.