Cursor Set Condition Variable

• macbaze72

  SSC Enthusiast

  Points: 120

  More actions

  April 13, 2018 at 9:54 am

  #410415

  In the code below, how can I only do the SET @Count= @Count + 1 if @Group changes? So if the @Group = Primary for 4 cursors, then the SET @Count= @Count + 1 does not happen until the @Group changes to "Third" or whatever?

  Thanks,
  EB

  DECLARE
     @Count INT
   , @Severity INT
   , @CustomerNumber NVARCHAR(5)
   , @Group NVARCHAR(2)SELECT
  @Count = MAX(Num)
  FROM
  Table1

  DECLARE TCur CURSOR LOCAL STATIC READ_ONLY FOR
   SELECT
     CustomerNumber
   , Group
   FROM
   Customer

  SET @Severity = 0
   OPEN TCur
   WHILE @Severity = 0

   BEGIN
     
   FETCH TCur INTO  
    @CustomerNumber
     , @Group
    
   IF @@FETCH_STATUS <> 0 BREAK  UPDATE Customer
   SET PrintGroup = @Count
   FROM Customer
   Where Group = @Group

   SET @Count= @Count + 1

   ENDCLOSE TCur
  DEALLOCATE TCur

• sestell1

  SSChampion

  Points: 10230

  More actions

  April 13, 2018 at 10:30 am

  #1986788

  Add another variable to hold the previous group value, so you can store the current and previous values in variables.
  On each loop, update current value via cursor, compare it to the previous value, compare them to set count appropriately, set the previous value to the current value, and loop again.
  Be sure to set the initial value of the previous group variable to some dummy value (something other than null).

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 13, 2018 at 11:30 am

  #1986799

  What this code is trying to attempt could be easily rewritten by something like this:
  
WITH cteCustomer AS(
  SELECT CustomerNumber
   , Group
   , PrintGroup
   , DENSE_RANK() OVER(ORDER BY Group) GroupCount
  FROM Customer
)
UPDATE cteCustomer
  SET PrintGroup = GroupCount;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2