CODEWITHSUNDEEP
sqlsql-server-2008
ExceptionLimeCat
ExceptionLimeCat

Reputation: 6400

SQL - CURSOR not fetching values properly

I'm new to cursors so I'm not sure if I'm doing this correctly but I'm trying to loop thru a temp table foreach row and compute the value for Column_Y using Column_X and another value from a variable. The variable value is correct but I'm not getting the values for Column_X from the cursor. See below:

SQL:

DECLARE @id INT
DECLARE @val DECIMAL
DECLARE @percent DECIMAL

DECLARE my_cursor CURSOR FOR 
SELECT a.Id, a.Val FROM #Temp_Tbl a

OPEN my_cursor

FETCH FROM my_cursor INTO @id, @val

WHILE @@FETCH_STATUS = 0
BEGIN
 --@Total is of type DECIMAL & is populated previously
 PRINT Convert(varchar,@id) + ': (' + Convert(varchar,@val) + '/' + Convert(varchar,@Total) + '),'
 SELECT @percent = @val / @Total
 UPDATE #Temp_Tbl SET PERCENT_OF = @percent WHERE Id = @id
 FETCH NEXT FROM my_cursor INTO @id, @val
END

CLOSE my_cursor
DEALLOCATE my_cursor

RESULTS:

| ID | Val | PERCENT_OF |
| 1  | 25  | 1          |
| 2  | 45  | 0          |
| 3  | 30  | 0          |

Messages Output:

2: (1/200) --NOTE: It's not printing the first record but 1 does get updated in the
3: (0/200) --PERCENT_OF column in the first row & 0 in the second.

Can anyone tell where I'm going wrong here? Thanks for any help.

Upvotes: 1

Views: 2072

Answers (1)

Lamak
Lamak

Reputation: 70648

The problem is that you are using DECIMAL without telling it the precision and scale. How many decimals do you want?. You should always explicitly use the precision and scale. Try something like this:

DECLARE @id INT
DECLARE @val DECIMAL(16,4)
DECLARE @percent DECIMAL(16,4)

Upvotes: 1

Related Questions