Results 1 to 6 of 6

Thread: SQL cursor

  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: SQL cursor

    I have three different check numbers , let s say (CheckNum 1 = 320, CheckNum2 = 201 CheckNum3 = 350) that I need to increment by 1 based on a condition if Counter_Name ='A_FILE' increment by 1 or Counter_Name ='G_FILE' increment by 1 or Counter_Name ='P_FILE'
    using cursor this is what I have :


    Declare @CheckNum as Int
    Select @CheckNum = Counter_Num From [Counter] WHERE Counter_Name ='A_FILE' or Counter_Name ='G_FILE' or Counter_Name ='P_FILE'

    DECLARE cursor1 CURSOR Forward_Only for
    select Counter_Num from [counter]
    OPEN cursor1

    FETCH NEXT FROM cursor1 INTO @CheckNum
    WHILE @@FETCH_STATUS = 0
    BEGIN

    Select @CheckNum = @CheckNum + 1
    FETCH NEXT FROM cursor1 INTO @CheckNum
    End


    CLOSE cursor1
    DEALLOCATE cursor
    But is not working, please help.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Do you really need a cursor just to do an increment??

    Are the checknum[i] variables all part of 1 row or are each a distinct row in the table?

    Code:
    CREATE TABLE CheckNumbers
    (
         Counter_Name char(6)
       , CheckNum1 int
       , CheckNum2 int
       , CheckNum3 int
    )
    or is it

    Code:
    CREATE TABLE CheckNumbers
    (
       Counter_Name char(6),
       CheckNum int
    )
    Of course the first table violates 3NF, but that is a different question.

    An Update statement of something like this should do the trick:

    Code:
    UPDATE CheckNumbers SET
         CheckNum1 = CheckNum1 + 1
       , CheckNum2 = CheckNum2 + 1
       , CheckNum3 = Checknum3 + 1
     WHERE Counter_Name in ('A_FILE', 'G_FILE', 'P_FILE')
     --WHERE Counter_Name Like '%FILE'
    A slight variation of the update statement is needed if your table is of the second form:

    Code:
    Update CheckNumbers SET
        CheckNum = CheckNum + 1
     WHERE Counter_Name in ('A_FILE', 'G_FILE', 'P_FILE')
     --WHERE Counter_Name Like '%FILE'
    Posting your DDL for the table would help if what I have provided is completely off base.

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    Thanks LinksUp

    This is my table:
    CREATE TABLE [Counter](
    [Counter_ID] [int] IDENTITY(0,1) NOT NULL,
    [Counter_Name] [nvarchar](100) NULL,
    [Counter_Num] [int] NULL,
    [UserID] [int] NULL,
    [LAST_UPDATED] [smalldatetime] NULL
    )
    What I need to do is increment [Counter_Num] by 1 if [Counter_ID] = 0 and keep [Counter_Num] the same at [Counter_ID] 1 and 2, so basically see which [Counter_ID] is selected and the increment only the corresponding [Counter_Num]

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Code:
    UPDATE Counter 
       SET
       Counter_Num = Counter_Num + 1
    WHERE Counter_ID = 0

    This will only update the one record that has Counter_ID equal to 0. If you want to update other Counter_ID's, you will have to use a variable and pass it to update statement.

    I'm not sure how Counter_Name comes into play based on your 2nd post. But the above will do exactly what you requested!

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Create sequence

    Google the CREATE SEQUENCE statement. Set up one of them for each checking account and use it.

  6. #6
    Join Date
    Feb 2013
    Posts
    46
    Thanks a lot that helped!

Posting Permissions

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