Cant iterate to the next account

  • Hi All,

    I've seem to have a brain cramp and can't get this to stored procedure to work:

    My problem is I can't remember how to reset the variables to iterate to the next value for example:

    IF @acctno != @last_acctno

            IF @i != @last_i

    How do I evaluate the previous value with the new value??

     

    Thanks in Advance

     

    CREATE    PROCEDURE spc_storedprocedure< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    (

    @TableName   VARCHAR(20),

    @TopValue       VARCHAR(10)

     

    )

     

    AS

     

    DECLARE @Error      INT

     

    SET NOCOUNT ON

     

                BEGIN

                            DECLARE

                                        @acctno   INT,

                                        @i       VARCHAR(14),

                                        @no             FLOAT

     

                                        DECLARE

                                                    @sSQL                       VARCHAR(8000),

                                                    @sCursor        VARCHAR(8000),

                                                    @last_acctno            INT,

                                                    @last_i VARCHAR(10)

                                       

                                                    SET @sSQL = ''

                                                    SET @sSQL =            'SELECT DISTINCT

                                                                                        acctno,

                                                                                        i,

                                                                                        no

                                                                            FROM '          

                                                                            + RTRIM(@TableName) +

                                                                            ' ORDER BY acctno, i;'

                                       

                                                    SET @sCursor = ''

                                                    SET @sCursor = 'DECLARE VARCursor CURSOR FOR ' + @sSQL

                                       

                                                    EXEC(@sCursor)

               

                            OPEN VARCursor

                                        FETCH NEXT FROM VARCursor INTO @acctno,@i,@no

                                        WHILE(@@FETCH_STATUS = 0)

                                                    BEGIN

                                                                IF @acctno != @last_acctno

                                                                            IF @i != @last_i

                                                                                       

                                                                                       PRINT @acctno      

                                                                                       -- Will be executing another sp passing params vars                                                                                                                             

                                                                                        FETCH NEXT FROM VARCursor INTO @acctno, @i, @no

                                                    END

                                         

               

                            CLOSE VARCursor

                            DEALLOCATE VARCursor

                END

     

    SET @Error = @@ERROR

     

    IF @Error <> 0

                BEGIN

                            RETURN @Error

                END

     

    RETURN @Error

    GO

     

  • Base don what I see so far, for your first time through you are going to execute your code in the IF's.  After executing you need to Set @last_acctno = @acctno and Set @last_i = @i then do your FETCH_NEXT.  Like this:

     WHILE(@@FETCH_STATUS = 0)

     BEGIN

      IF @acctno != @last_acctno

       BEGIN

        IF @i != @last_i

         BEGIN

                                       PRINT @acctno     

          -- Will be executing another sp passing params vars                                                                                                                              

          Set @last_acctno = @acctno

          Set @last_i = @i

         END

       END

     

      FETCH NEXT FROM VARCursor INTO @acctno, @i, @no

     END

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Thanks,

    This works

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply