sql2000 loop in a stored procedure

Question

I am very new to SQL i can work with basic statements fairly easily but i haven't figured out loops yet.

Foreach(JobHeaderID AS @OldJobHeaderID in dbo.EstimateJobHeader WHERE EstimateID=@OldEstimateID)  
{  
  INSERT EstimateJobHeader (ServiceID,EstimateID) 
  SELECT ServiceID, @NewEstimateID 
  FROM EstimateJobHeader 
  WHERE EstimateID=@OldEstimateID;  

  SELECT @err = @@error 
  IF @err <> 0 
  BEGIN 
    ROLLBACK TRANSACTION 
    SET @RETURN_VALUE = 4 
    RETURN 4 
  END  

  SET @NewJobHeaderID = CAST(SCOPE_IDENTITY() AS INT)  

  SELECT @err = @@error 
  IF @err <> 0 
  BEGIN 
    ROLLBACK TRANSACTION 
    SET @RETURN_VALUE = 3 
    RETURN 3 
  END  

  INSERT EstimateDetail (JobHeaderID, OtherCols) 
  SELECT (@NewJobHeaderID,OtherCols) 
  FROM EstimateDetail 
  WHERE JobHeaderID=@OldJobHeaderID

  SELECT @err = @@error 
  IF @err <> 0 
  BEGIN 
    ROLLBACK TRANSACTION 
    SET @RETURN_VALUE = 3 
    RETURN 3 
  END  

  INSERT EstimateJobDetail (JobHeaderID, OtherCols) 
  SELECT (@NewJobHeaderID, OtherCols) 
  FROM EstimateJobDetail 
  WHERE JobHeaderID=@OldJobHeaderID  

  SELECT @err = @@error 
  IF @err <> 0 
  BEGIN 
    ROLLBACK TRANSACTION 
    SET @RETURN_VALUE = 3 
    RETURN 3 
  END  
}

Answer 1

Take a look at the WHILE statement:

http://msdn.microsoft.com/en-us/library/aa260676(SQL.80).aspx

However, depending on what you are trying to do, there is probably a better, set-based way to do what you are trying to do, and should consider that first.

Answer 2

You should avoid loops in stored procedures.

Sql is a declarative language, rather than the imperative languages you are most used to. Nearly everything you want to do with a loop should either be done as set-based operation or done in client code. There are, of course, exceptions, but not as many as you think.

See this:
http://stackoverflow.com/questions/588050/why-is-it-so-difficult-to-do-a-loop-in-t-sql

---

You asked how to do it using set-based methods. I'll do my best, but there's a bug early in your code that makes it hard to be sure I'm reading it right. The condition on the first INSERT statement matches the condition on the FOREACH loop. So either the loop will only ever run once (one record returned there), or your insert is inserting several new records per iteration (yes, insert statements can add more than one record at a time). And if it's adding several records, why do you only get the identity created by the last insert?

That said, I think I understand it well enough to show you something. It looks like you're just making a copy of an estimate. You also don't explain where the @NewEstimateID value comes from. If there's a parent table then so be it, but it would be helpful to know about.

/* Where'd @NewEstimateID come from? */
/* If there are several records in EstimateJobHeader with @OldEstimateID,
 *  this will insert one new record for each of them */
INSERT EstimateJobHeader (ServiceID,EstimateID)
     SELECT ServiceID, @NewEstimateID 
     FROM EstimateJobHeader
     WHERE EstimateID= @OldEstimateID

/* Copy EstimateDetail records from old estimate to new estimate */
INSERT EstimateDetail (JobHeaderID, OtherCols) 
    SELECT (new.JobHeaderID,ed.OtherCols) 
    FROM EstimateJobHeader new
    INNER JOIN EstimateJobHeader old ON old.EstimateID= @OldEstimateID 
        AND new.EstimateID= @NewEstimateID AND old.ServiceID=new.ServiceID
    INNER JOIN EstimateDetail ed ON ed.JobHeaderID= old.JobHeaderID

/* Copy EstimateJobDetail records from old estimate to new estimate */
INSERT EstimateJobDetail (JobHeaderID, OtherCols)
    SELECT (new.JobHeaderID,ed.OtherCols) 
    FROM EstimateJobHeader new
    INNER JOIN EstimateJobHeader old ON old.EstimateID= @OldEstimateID 
        AND new.EstimateID= @NewEstimateID AND old.ServiceID=new.ServiceID
    INNER JOIN EstimateJobDetail ejd ON ejd.JobHeaderID= old.JobHeaderID

The code above makes the assumption the ServiceID+EstimateID is unique within the EstimateJobHeader table. If this is not the case, I need to know what column or columns do uniquely identify rows in the table, so that I can join the old record with the new and be certain the relationship is 1:1.

Finally, error checking was omitted for brevity.