title: Loop in SQL Server without a Cursor
body: <p>Below is some SQL Server code I have been working on. I know now that using a cursor is a bad idea in general, but I cannot figure out how else I can make this work. The performance is terrible with the cursor. I'm really just using some simple IF statement logic with a loop, but can't translate it to SQL. I'm using SQL Server 2012.</p>
  
  <pre><code>IF [Last Employee] = [Employee] AND [Action] = '1-HR'
      SET [Employee Record] = @counter + 1
  ELSE IF [Last Employee] != [Employee] OR [Last Employee] IS NULL
      SET [Employee Record] = 1
  ELSE
      SET [Employee Record] = @counter
  </code></pre>
  
  <p>Basically, how can I keep this @counter going without a cursor. I feel like the solution is simple, but I've lost myself. Thanks for looking.</p>
  
  <pre><code>declare curr cursor for
  select WORKER, SEQUENCE, ACTION
  FROM [DB].[Transactional History]
  order by WORKER ,SEQUENCE asc
  
  declare @EmployeeID as nvarchar(max);
  declare @SequenceNum as nvarchar(max);
  declare @LastEEID as nvarchar(max);
  declare @action as nvarchar(max);
  declare @currentEmpRecord int
  declare @counter int;
  
  open curr
  fetch next from curr into @EmployeeID, @SequenceNum, @action;
  while @@FETCH_STATUS=0
  
  begin 
      if @LastEEID=@EmployeeID and @action='1-HR'
      begin
          set @sql = concat('update [DB].[Transactional History]
          set EMPRECORD=',+ @currentEmpRecord, '+1 
          where WORKER=', @EmployeeID, ' and SEQUENCE=', @SequenceNum)
          EXECUTE sp_executesql @sql 
          set @counter=@counter+1;
          set @LastEEID=@EmployeeID;
          set @currentEmpRecord=@currentEmpRecord+1;
      end
      else if @LastEEID is null or @LastEEID&lt;&gt;@EmployeeID
          begin
              set @sql = concat('update [DB].[Transactional History]
              set EMPRECORD=1
              where WORKER=', @EmployeeID, ' and SEQUENCE=', @SequenceNum)
              EXECUTE sp_executesql @sql
              set @counter=@counter+1;
              set @LastEEID=@EmployeeID;
              set @currentEmpRecord=1
          end
      else
          begin
              set @sql = concat('update [DB].[Transactional History]
              set EMPRECORD=', @currentEmpRecord, ' 
              where WORKER=', @EmployeeID, ' and SEQUENCE=', @SequenceNum)
              EXECUTE sp_executesql @sql
              set @counter=@counter+1;
          end
      fetch next from curr into @EmployeeID, @SequenceNum, @action;
      end
  
  close curr;
  deallocate curr;
  </code></pre>
  
  <p>Below is code to build a sample table. I want to increase EMPRECORD every time a record is '1-HR', but reset it for each new WORKER. Before this code is executed, EMPRECORD is null for all records. This table shows the target output.</p>
  
  <pre><code>CREATE TABLE [DB].[Transactional History-test](
      [WORKER] [nvarchar](255) NULL,
      [SOURCE] [nvarchar](50) NULL,
      [TAB] [nvarchar](25) NULL,
      [EFFECTIVE_DATE] [date] NULL,
      [ACTION] [nvarchar](5) NULL,
      [SEQUENCE] [numeric](26, 0) NULL,
      [EMPRECORD] [numeric](26, 0) NULL,
      [MANAGER] [nvarchar](255) NULL,
      [PAYRATE] [nvarchar](20) NULL,
      [SALARY_PLAN] [nvarchar](1) NULL,
      [HOURLY_PLAN] [nvarchar](1) NULL,
      [LAST_MANAGER] [nvarchar](255) NULL
  ) ON [PRIMARY]
  
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'EMP-Position Mgt', CAST(N'2004-01-01' AS Date), N'1-HR', CAST(1 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', N'Hourly', NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Change Job', CAST(N'2004-05-01' AS Date), N'5-JC', CAST(2 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'4', NULL, NULL, NULL, N'3')
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'EMP-Terminations', CAST(N'2005-01-01' AS Date), N'6-TR', CAST(3 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'4', NULL, NULL, NULL, N'4')
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Change Job', CAST(N'2010-05-01' AS Date), N'5-JC', CAST(4 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', NULL, NULL, NULL, N'4')
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'EMP-Position Mgt', CAST(N'2011-05-01' AS Date), N'1-HR', CAST(5 AS Numeric(26, 0)), CAST(2 AS Numeric(26, 0)), N'3', N'Hourly', NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'CWR-Position Mgt', CAST(N'2012-01-01' AS Date), N'1-HR', CAST(6 AS Numeric(26, 0)), CAST(3 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Organizations', CAST(N'2015-01-01' AS Date), N'3-ORG', CAST(7 AS Numeric(26, 0)), CAST(3 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Organizations', CAST(N'2015-01-01' AS Date), N'3-ORG', CAST(8 AS Numeric(26, 0)), CAST(3 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'EMP-Terminations', CAST(N'2001-01-01' AS Date), N'6-TR', CAST(9 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'EMP-Terminations', CAST(N'2001-05-01' AS Date), N'6-TR', CAST(10 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'Change Job', CAST(N'2004-01-01' AS Date), N'5-JC', CAST(11 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', NULL, NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'Change Job', CAST(N'2004-01-01' AS Date), N'5-JC', CAST(12 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', NULL, NULL, NULL, N'3')
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'EMP-Position Mgt', CAST(N'2014-01-01' AS Date), N'1-HR', CAST(13 AS Numeric(26, 0)), CAST(2 AS Numeric(26, 0)), N'4', N'Salary', NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'3', NULL, N'EMP-Terminations', CAST(N'2012-01-01' AS Date), N'6-TR', CAST(14 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL)
  GO
  INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'4', NULL, N'EMP-Position Mgt', CAST(N'2012-01-01' AS Date), N'1-HR', CAST(15 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL)
  GO
  
  select * from DB.[Transactional History-test]
  </code></pre>