title: Rewrite stored procedure to remove cursors
body: <p>I'm attempting to remove the cursors from this stored procedure but not sure of the Best latest best practise for this kind of operational to run in a efficient statement.</p>
  
  <p>Can anyone offer any pseudo code on what to implement to eliminate these from a Dev perspective?</p>
  
  <pre><code>    --Generate the channel date from a specified date
  DECLARE @ConvDate DATETIME
  SET @ConvDate = DateAdd(day,-100,getDate())
  WHILE DateDiff(day,GetDate(), @ConvDate ) &lt; 0 
  BEGIN
      EXEC mltGenerateChannelData @ConvDate
      SET @ConvDate = DateAdd(day, 1, @ConvDate)
  END
  
  
          CREATE PROCEDURE [dbo].[mltGenerateChannelData] (@ConvDate DATETIME)  AS  
          BEGIN  
  
             DECLARE @ChannelId INT, 
             @URLSignature Varchar(30), 
             @RawSQL VARCHAR(2000), 
             @SQLQuery VARCHAR(4000), 
             @ThisUTMId BIGINT 
  
             DECLARE cursChannels CURSOR STATIC FOR 
              SELECT 
                  ChannelId, 
                  URLSignature, 
                  RawSQL  
              FROM dbo.TrackingChannel_tbl (NOLOCK) 
              WHERE ProcessVisitDate = 1  
  
              SET @ConvDate = dbo.datePart_fn(@ConvDate)  
  
           --Clear out any existing data for this conversion date  
           DELETE FROM TrackingChannelDailyTotal_tbl 
           WHERE TrackingDate = @ConvDate  
  
           OPEN cursChannels  
  
           FETCH cursChannels INTO @ChannelId, @URLSignature, @RawSQL  
  
           CREATE TABLE #UTM 
           (trpUTMID BIGINT PRIMARY KEY, 
           TotalMArgin MONEY, 
           RawURLRequest Varchar(2000), 
           Keywords VARCHAR(1000), 
           VisitDate DATETIME, 
           RefererURL VARCHAR(2000))  
  
           INSERT INTO #UTM (trputmid, TotalMargin)  
              SELECT trpUTMID, SUM(b.TotalMArgin)  
              FROM TrackingConversion_tbl c(NOLOCK), Booking_tbl b(NOLOCK)               
              WHERE c.BookingId = b.BookingId  
              AND c.BookedDate &gt;= @ConvDate  
              GROUP BY trputmid  
  
           UPDATE u  
              SET RawURLRequest = v.RawURLRequest,  
               Keywords = v.Keywords,  
                     VisitDate = v.VisitDate,  
                     RefererURL = v.RefererURL  
               FROM #UTM u,    
              TrackingVisit_tbl (NOLOCK)  v   
           WHERE v.trpUTMID = u.trpUTMId  
  
           CREATE TABLE #UTM2  (trpUTMID BIGINT PRIMARY KEY)  
  
           WHILE @@FETCH_STATUS = 0  
           BEGIN  
  
             Print 'Processing Channel Id : ' + Convert(varchar(10), @ChannelId)  
  
             TRUNCATE TABLE #UTM2  
  
  
             SET @SQLQuery = ' INSERT INTO #UTM2 (trpUTMId)  
                               SELECT u.TrpUTMId  
                               FROM #UTM u               
                               WHERE u.VisitDate &gt;= ''' + COnvert(varchar,@ConvDate) + '''   
                               AND u.VisitDate &lt; DateAdd(day,1,''' + Convert(varchar,@ConvDate) + ''') '  
  
  
             IF @URLSignature &lt;&gt; ''  
             BEGIN              
              SET @SQLQuery = @SQLQuery + 'AND u.RawURLRequest like ''%' + @URLSignature + '%'' '    
             END  
  
             IF @RawSQL &lt;&gt; ''  
             BEGIN  
              SET @SQLQuery = @SQLQuery + @RawSQL  
             END  
  
             EXEC (@SQLQuery)  
  
             INSERT INTO TrackingChannelDailyTotal_tbl (ChannelId, TrackingDate, Conversions, TotalMargin)  
              SELECT @ChannelId, @ConvDate, Count(u1.trpUTMID), IsNUll(SUM(TotalMargin),0)  
              FROM #UTM u1, #UTM2 u2  
              WHERE u1.TRputmid = u2.trputmid  
  
  
             FETCH cursChannels INTO @ChannelId, @URLSignature, @RawSQL  
  
           END  
  
           CLOSE cursChannels  
           DEALLOCATE cursChannels  
  </code></pre>