From: esquel@sommarskog.se   
      
   phdate (drscrypt@gmail.com) writes:   
   > The state changes are a by-product of my attempts to get it working in   
   > lieu of alternatives involving cursors.  Other solutions that avoid the   
   > state change are most welcome.   
      
   Well, you need to update the number generator, so that is a state   
   change.   
      
   > Well, it is needed in several places, and I was looking for a way to   
   > avoid copying code blocks all over the place and instead have something   
   > that could be named.  If you think there is a better alternative, please   
   > do show some code.  I can seek to relax the requirements on my end.   
      
   Say that you need to insert a number of rows in a table, with each   
   row being assigned a unique, sequential, id, the typical construct is:   
      
      BEGIN TRANSACTION   
      
      SELECT @nextid = coalesce(MAX(id), 1) FROM tbl WITH (UPDLOCK)   
      
      INSERT tbl (id, ....   
         SELECT @nextid + row_number() OVER (ORDER BY ...), ...   
         FROM   
      
      COMMIT TRANSACTION   
      
   Whether that fits in the context you are working, I don't know.   
   Furthermore, this solution requires SQL 2005 or higher.   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)