Variable doesn't seem to store value

[p8ass8]

I am trying to print the value of variable @NonSmoker by reading this float value from a table using a cursor. The print statement isn't returning anything. It seems the cursor isn’t reading or storing any values for @NonSmoker. What could the problem be?? Here is my script;

create procedure smoking_history as
begin
declare @NonSmoker int, @ID int
set @NonSmoker=0
set @ID=0
declare smokinghistory_cursor cursor for
select C.ID, D.NonSmoker from CView C
inner join TestCases D on C.RefNbr=D.IDNbr;

open smokinghistory_cursor

fetch next from smokinghistory_cursor into @ID, @NonSmoker
while @@fetch_status=0
begin

print @NonSmoker

fetch next from smokinghistory_cursor into @ID, @NonSmoker

end
close smokinghistory_cursor
deallocate smokinghistory_cursor
end

 

[SQLSister]

check and see if your select is returning anything?

Also you have declared @nonsmoker as an int not a float. Personally I would not allow anything ever to be a float but if that is what is in your database, perhaps delaring the variable as a float as well would make it work better. I make it a policy to declare variables the same data type
as the data I will be filling them with unless there is some good reason not to.

And what are you ultimately going to use a cursor for? You know that they are to be avoided at all costs, right? Don;t use it if you are going to update, insert or delete records unless you want to have performance issues.

"NOTHING is more important in a database than integrity." ESquared

 

[p8ass8]

I am using the cursor to insert records into a new table whenever NonSmoker value is found to be a 1.
I just realised that the while loop, for some reason, is reading only the first 100 records, for which, coincidentially, the NonSmoker values are NULL! So it makes sense that there are no results displayed for the print statement.

But here is my question : I have no idea why it would break out of the loop before reading all the 500 records in the table. What could possibly make the while loop break at 100?? I am only retrieving NonSmoker values from the table, if it acknowledges the first 100 that are null, then why not move forward to read the actual 1/0 values that occur later.

Also, I did change the int to a float in my script(thanks!). I don't know why this was set to float in the first place when the values are either 1/0/null!!

 

[gmmastros]

Are you sure you are joining the 2 tables on the correct field?

[tt][blue]
select C.ID, D.NonSmoker from CView C
inner join TestCases D on [!]C.RefNbr=D.IDNbr[/!]
[/blue][/tt]

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

Oh... while we are at it...

Based on your stated objectives, you can probably improve performance (and complexity) by using a set based approach to solve this problem. I suggest you state your objective and show some sample data and expected results. With this information, we can probably show you a method to achieve the same results without using a cursor and it will perform better too.


-George

"the screen with the little boxes in the window." - Moron