SQL replace cursor with while loop

The key here is to get all the item categories which fit our WHERE clause into a memory table and use the Primary Key of this table to pick up each successive item category.

   1:  --Declare variables

   2:  DECLARE @item_category_id INT

   3:  DECLARE @order_id INT

   4:  DECLARE @purchase_order_id INT

   5:   

   6:  --Declare a memory table

   7:  DECLARE @item_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL, --THE IDENTITY STATEMENT IS IMPORTANT!

   8:  item_category_id INT,

   9:  order_id INT

  10:  )

  11:   

  12:  --now populate this table with the required item category values

  13:  INSERT INTO @item_table

  14:  SELECT  -- Same SELECT statement as that for the CURSOR

  15:  it.item_category_id

  16:  ,ord.order_id

  17:  FROM dbo.item_categories it

  18:   

  19:  INNER JOIN dbo.orders ord

  20:  ON ord.item_category_id = it.item_category_id

  21:   

  22:  WHERE ord.order_date >= '1-sep-05'

  23:  and it.isSuspended != 1                     

  24:   

  25:  DECLARE @item_category_counter INT

  26:  DECLARE @loop_counter INT

  27:   

  28:  SET @loop_counter = ISNULL(SELECT COUNT(*) FROM @item_table),0) -- Set the @loop_counter to the total number of rows in the

  29:  -- memory table

  30:   

  31:  SET @item_category_counter = 1

  32:   

  33:  WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter

  34:  BEGIN

  35:  SELECT @item_category_id = item_category_id

  36:  ,@order_id = order_id

  37:  FROM @item_table

  38:  WHERE primary_key = @item_category_counter

  39:   

  40:  --Now pass the item-category_id and order_id to the OUTPUT stored procedure

  41:  EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT

  42:   

  43:  /*

  44:  Call other code here to process your pruchase order for this item

  45:  */

  46:   

  47:  SET @item_category_counter = @item_category_counter + 1

  48:  END