Preventing Multiple recordsets when using cursors

• Stuart Mackenzie

  SSC Journeyman

  Points: 91

  More actions

  May 28, 2008 at 9:50 am

  #72107

  1) it is not possible for me to simple select the recordset that i want, i have to only have one recordset returned

  The stored proc is like this

  for the sake of argument..

  tblHats contains information about a Hat which is uniquely identified

  tblHatHistory contains information about inspections that have been performed on Hats

  for each record in tblHats there will be zero to n corresponding entries in tblHatHistory

  tblHats:

  HatID(PK)

  HatName

  tblHatHistory

  HatID

  InspectedDate

  I wish to copy tblHats to a #temp file, adding an 'Inspected' field

  Then iterate tblHatHistory using a cursor to populated the 'Inspected' field with the date of the most recent inspection on the hat

  Then Select data from the updated #temp table

  This i have working, but the cursor returns a one line recordset, before the recordset which i require. This despite Set Nocount on.

  Is there a way to stop the cursor from returning a recordset as a result of calling the SP, or is there a way to perform a query which will achieve this.

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  May 28, 2008 at 10:42 am

  #821315

  SELECT H.HatID, H.HatName, MAX( His.InspectedDate ) AS LastInspectedDate

  FROM tblHats H

  LEFT JOIN tblHatHistory His ON H.HatID = His.HatID

  GROUP BY H.HatID, H.HatName

  --Ramesh
  

  ---

• Stuart Mackenzie

  SSC Journeyman

  Points: 91

  More actions

  May 29, 2008 at 3:41 am

  #821654

  ahh thankyou. that does exactly what i needed 🙂