Smokes your problems, coughs fresh air.

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

  1. Rowan Rodrik

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

  2. halfgaar

    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.

© 2024 BigSmoke

Theme by Anders NorenUp ↑