Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: How to convert cursor base sp into set based simple stored proc...

    Hey All,
    I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues.Please advise how can I convert this stored proc into set base simple statment.
    Code:
    ALTER Procedure [ABSP]
    @Var1  varchar(20),
    @Var2  varchar(3),
    @Var3  varchar(2) = 'Dy'           
    As
    declare @selectlist varchar(5000)
    declare @tableBuild varchar(1000)
    declare @FieldName varchar(50)
    declare @FieldSelect varchar(500)
    declare @FieldTitle varchar(50)
    declare @TableName varchar(50)
    declare @holdTable varchar(50)
    declare @title varchar(50)
    declare @holdTitle varchar(50)
    declare @PageName varchar(50)
    declare @sequence varchar(100)
    declare @extraCriteria varchar(200)
    declare @holdCriteria varchar(200)
    declare @insertSQL varchar(5000)
    declare @ConvertRoutine varchar(500)
    declare @loopCtrl1 bit
    declare @loopCtrl2 bit
    declare @ConvertSQL varchar(5000)
    declare @PrevValue varchar(50)
    declare @NewValue varchar(50)
    declare @ActionTxt varchar(1)
    declare @Description varchar(20)
    declare @effDate varchar(10)
    declare @transEffDate varchar(10)
    declare @expDate varchar(10)
    declare @lastTransDate varchar(10)
    declare @policyStatus varchar(2)
    declare @reasAmendDesc varchar(50)
    declare @policyNumber varchar(20)
    declare @riskState varchar(20)
    declare @PriorPrem money  
    declare @AmendPrem money  
    declare @PremDiff money  
    declare mtcursor cursor for
    select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1
       where Column1 = @Var2
      order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName
     open mtcursor
     fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
    set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
    set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
    set @loopCtrl1 = 0
    set @loopCtrl2 = 0
     WHILE (@loopCtrl1 = 0)
     begin
      set @holdTable = @TableName 
      set @holdCriteria = @extraCriteria
      set @holdTitle = @title
      if @FieldSelect = ''
          set @selectlist = @selectlist + ',' + @FieldName
      else
          set @selectlist = @selectlist + ',' + @FieldSelect
      set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)'
      fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
      if @@fetch_status <> 0
          set @loopCtrl2 = 1
      if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1)
       begin
        set @tableBuild = @tableBuild + ')'
        set @insertSQL = '
     declare mtcursor2 cursor for
      select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1
      where TableName = ''' + @holdTable + '''
          and ExtraCriteriaTxt = ''' + @holdCriteria + '''
          and PageTitleTxt = ''' + @holdTitle + '''
          and Column1 = ''' + @Var2 + '''
          order by FieldDisplaySequenceNbr
     declare @FieldName varchar(50)
     declare @FieldTitle varchar(50)
     declare @ExtraUpdateMatch varchar(500)
     declare @PullUpdate bit
     declare @PullAdd bit
     declare @PullDelete bit
     declare @PullAnyUpdate bit
      open mtcursor2
      fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
      WHILE (@@fetch_status = 0)
      begin
        if substring(@FieldTitle,1,1) = ''#''
            set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1)
                    else
                        set @FieldTitle = '''''''' + @FieldTitle + ''''''''
       if @PullAnyUpdate = 1
                      begin
                exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' 
          from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
            where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''')
               end
       else
          begin
      if @PullUpdate = 1
                 exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' 
           from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
             where A.Val1 = ''''O''''  and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null) 
              or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '')
                    end
       if @PullAdd = 1
      exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A''''       from #tempTable A where Val1 = ''''A'''''')
       if @PullDelete = 1
        exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D'''' 
          from #tempTable A where Val1 = ''''D'''''')
       fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
      end
      close mtcursor2
      deallocate mtcursor2'
        exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL)
        set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
        set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
       end
      if @loopCtrl2 = 1
           set @loopCtrl1 = 1
      end
      close mtcursor
      deallocate mtcursor
      Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 
      declare deletecursor cursor for
     select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U'
     open deletecursor
     fetch next from deletecursor into @PageName
     while @@fetch_status = 0
     begin
      if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0 
           DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U'
      fetch next from deletecursor into @PageName
        end
      close deletecursor
      deallocate deletecursor
      declare convertcursor cursor for
     select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a
     inner join MyTable1 b
      on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> ''
     where a.Id = @Var1 
      open convertcursor
      fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
     while @@fetch_status = 0
         begin
     set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)'
     set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) '
     set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
     set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''
     if @ActionTxt = 'A'
      set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + ''''
     else
       begin
      set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + ''''
      set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')'  
       end
     if @ActionTxt = 'D'
      set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + ''''
     else
       begin
      set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + ''''
      set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')' 
       end
     set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted
      where EntrySequenceNbr = ''' + @Sequence + ''''
     exec (@ConvertSQL) 
     fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
        end
      close convertcursor
      deallocate convertcursor
      if @Var2 = 'dummy2 '
      --exec PAConfirmCovConversions @Var1 = @Var1
     exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 
      Create table #pageSeqTable (PageTitle varchar(50), PageSeq int)
      insert into #pageSeqTable 
     select distinct PageTitleTxt, PageDisplaySequenceNbr 
     from MyTable1
     where Column1 = @Var2
      select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt
      from    MyTable2, #pageSeqTable b
      where Id = @Var1 and PageNm = b.PageTitle
      order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr
      select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101),
     @policyStatus = PolicyStatusCd, 
     @riskState = StateName,
     @AmendPrem = convert(money,PremiumAmount) 
      from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C
      where Id = @Var1
           AND Val2 = (select max(Val2)
          from SHPlanInfo
          where Id = @Var1)
           AND B.ReasonAmendedCd = A.ReasonAmendedCd
           AND C.StateCode = A.RiskState
    Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' 
    Set @PremDiff = @AmendPrem - @PriorPrem            
     select EffectiveDate = @effDate
      select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate
      select PriorPremium =  @PriorPrem 
      select AmendPremium = @AmendPrem 
      select PremiumDifference = @PremDiff
    Select ClientNumber from SHClient with (nolock) where Id=@Var1 and  ApplicantRecordInd = 1
    delete from MyTable2 where Id = @Var1
    return
    Last edited by gvee; 06-02-08 at 09:26. Reason: Added [CODE] tags on users behalf

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are loops within loops and calls to other sprocs here and you haven't even formatted the code (do you know how the code tags work?). Unless you are very fortunate and someone with lots of time turns up then you are either going to have to put in a lot of effort yourself or hire someone.

    What are the actual business rules? What does all that code do?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •