title: Better way of identifying ranges of numbers in SQL Server other than a cursor
body: <p>We have a SQL Server data base that holds service records.  Each service record has a service id.  The service ID is made up of the date, the client number, service rep, and then an incremental piece that goes up 1 every order for that day.  Also, our service techs can have more than one service record at any site, so there is also a Main Service ID assigned to the service ID as well.  The Main Service ID is first service ID for that site.  So the service ID and the main service ID can be the same if there is only one service being done at that site.  If there is more than one service being done, the service ID and main service ID would be different.  </p>
  
  <p>I’ve been tasked with seeing if there are any gaps in the service ids.  So, I created a query that identifies the lowest service ID for each Main Service ID.  I need a way to generate all the service ids between the service ID and main service ID for each section.  I’m using a cursor because I can’t think of another way to do this.  So my question is there has to be a better way to do what I am trying to do.  Could anyone point me in the right direction?</p>
  
  <p>Here is my Cursor:  </p>
  
  <pre><code>DECLARE @ServiceID as Varchar(12), 
          @ MainServiceID as Varchar(12),
          @TempSCR as Varchar(12)
  
  DECLARE db_RecSet CURSOR FOR 
  SELECT [ServiceID], Max([MainServiceID]) as MainServiceID
  FROM Service_Table
  WHERE MainServiceID like '12%'
  AND [MainServiceID] &lt;&gt; [ServiceID]
  GROUP BY [ServiceID]
  
  OPEN db_RecSet
  FETCH NEXT db_RecSet INTO @ServiceID, @ MainServiceID
  
  WHILE (@@FETCH_STATUS = 0)
      BEGIN
          SET @TempSCR = @ServiceID
          WHILE @TempSCR &lt;= @ MainServiceID
              BEGIN
              INSERT INTO [Temp_Identifiy_Possible_SCRHeader](ServiceID, MainServiceID)
                  VALUES (@TempSCR, @ MainServiceID)
              SET @TempSCR = @TempSCR +1
              END 
          FETCH NEXT FROM db_RecSet INTO @ServiceID, @ MainServiceID
      END
  CLOSE db_RecSet
  DEALLOCATE db_RecSet
  </code></pre>