troubleshooting Question

SQL Server 2014 Cursor in stored procedure never seems to move past the first record. I.e it does not loop

Avatar of Adimitro
Adimitro asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
2 Comments1 Solution111 ViewsLast Modified:
Examine the following piece of code:
        set @RM = 'some value'
        set @BASEQTY = 1
        DECLARE UOMSCHED CURSOR SCROLL FOR
      SELECT UOFM,EQUOMQTY FROM tablename WHERE column = @RM
      OPEN UOMSCHED
      FETCH first FROM UOMSCHED INTO @UOFM,@EQUOMQTY
        WHILE  @@FETCH_STATUS = 0
        BEGIN
                  set @cursor_Rows = @@Cursor_rows
                  SET @RMQTYCONVERTED = @BASEQTY /@EQUOMQTY
                  INSERT INTO RM_QTYS (RM_ID,[rmUOM],[QTYrequired])
                  VALUES (@RM,@UOFM,@RMQTYCONVERTED)
            END
      FETCH next FROM UOMSCHED INTO @UOFM,@EQUOMQTY
      CLOSE UOMSCHED;
      DEALLOCATE UOMSCHED;

In this example the SQL Query returns 5 rows. The @@cursor_rows variable also shows that the cursor has 5 rows.
However the variables @UOFM and @RMQTYCONVERTED always store the values of the first record. In other words the cursor does not seem to move to the next record. The purpose of this loop is to determine the quantity of something in 5 different units of measure. The units of measure and the conversion factors are stored in 'tablename'. All 5 calculated results are then inserted into another table 'RM_QTYS'. If there is a way to perform this without a cursor I am happy as well.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 2 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 2 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004