The following is an excerpt from a FoxPro program that uses "SET TEXTMERGE ON NOSHOW" and SQL Pass-thru to execute a cursor on SQL Server that updates one field of the cursor (from a table in TEMPDB). This query works (updating all rows) when executed directly in Query Analyzer. However, the same code run within a FoxPro program will update a variable number of rows each time I test it. Example, first run 25 rows modified, second run 27 rows updated, etc. Its interesting that the number of rows updated varies with each subsequent test run. For some reason, @@fetch_status is no longer 0, even though it hasn't updated all records.
set textmerge on noshow
text to sqltext
set cursor_close_on_commit off
declare @smacclmpymt decimal(15,2), @awprate decimal(11,5), @drugmacrate decimal(11,5), @lessstuff decimal(17,2) @new_amt_awp decimal(12,4)
declare @smac_rate decimal(12,4), @qty_disp char(9), @amt_awp decimal(11,5),
@copay decimal(16,2), @tpl_amt decimal(17,2), @amt_mac decimal(11,5), @amt_bill decimal(20,5),
@smac_flag char(1), @num_icn char(13), @cde_ndc char(11), @dte_dispen datetime
declare mycursor cursor for
select smac_rate, rtrim(ltrim(qty_disp)), amt_awp, copay, tpl_amt, amt_mac, amt_bill, smac_flag,
rtrim(ltrim(num_icn)), rtrim(ltrim(cde_ndc)), dte_dispen
from <
>
open mycursor
fetch next from mycursor
into @smac_rate, @qty_disp, @amt_awp, @copay, @tpl_amt, @amt_mac, @amt_bill, @smac_flag,
@num_icn, @cde_ndc, @dte_dispen
while @@fetch_status = 0
begin
set @smacclmpymt = 0
set @new_amt_awp = 0
set @awprate = 0
set @drugmacrate = 0
set @lessstuff = 0
set @smacclmpymt = @smac_rate * cast(ltrim(rtrim(@qty_disp)) as numeric)
set @new_amt_awp = @amt_awp * .80
set @lessstuff = @copay + @tpl_amt
set @awprate = (@new_amt_awp + 4.90) - @lessstuff
set @drugmacrate = @amt_mac
if @smac_rate < @awprate
if @smacclmpymt < @amt_bill
if @smac_rate < @drugmacrate
update <>
set smac_flag = 'Y'
where current of mycursor
else
update <>
set smac_flag = 'N'
where current of mycursor
else
update <>
set smac_flag = 'N'
where current of mycursor
else
update <>
set smac_flag = 'N'
where current of mycursor
fetch next from mycursor
into @smac_rate, @qty_disp, @amt_awp, @copay, @tpl_amt, @amt_mac, @amt_bill, @smac_flag,
@num_icn, @cde_ndc, @dte_dispen
end
close mycursor
deallocate mycursor
endtext
set textmerge off
