title: Stored Procedure Loop
body: <p>I have a stored procedure which generates some xml based on 3 parameters: @property_id, @property_component_id and @return_as_xml.  The value for @return_as_xml will be the same for all records which is 0.</p>
  
  <p>I have modified this and now need to run it for multiple Property records based on a specific condition i.e. for active and leased properties.  A one-many relationship exists between property and property_component, so a property can have multiple property_components and does have at least one corresponding record.  </p>
  
  <p>I some how need to do a select of the records I want based on the active and leased fields in the Property table.  Select the corresponding property_component_id from the property_component table where a corresponding property_id exists.  I then want to loop through each of the result rows, execute the stored procedure passing in new parameters for each row.  </p>
  
  <p>I don't want to modify the stored procedure more than I already have as it is executed from an asp.net console application which requires it to return xml for a single property, ie I don't want alter the stored procedure to return xml for multiple properties.</p>
  
  <p>My stored procedure execution is just the standard generated sql server code:</p>
  
  <pre><code>DECLARE @return_value int
  
  EXEC    @return_value = [dbo].[p_CPGUploader__FetchXMLForProperty_RCA]
      @property_id = 106016,
      @property_component_id = 108382,
      @return_as_xml = 0
  
  SELECT  'Return Value' = @return_value
  GO
  </code></pre>
  
  <p>How can I modify this to work for my described scenario, how can I get select a recordset then loop through it executing the stored procedure passing based on values from each row of the select?  I come from an asp.net background and my SQL Server skills are extremely limited so any assistance would be greatly appreciated.  I am using SQL Server 2008 R2 although I believe the original stored procedure was written in 2004 if that's of any relevance.</p>