Updateable Cursors

[raminriahi]

I'm using a cursor in a stored procedure which has been defined as

declare <cursor_name> cursor
for select <f1>,...,<fn>
from <table_name>
where <condition>
for update

I tried several options in declare cursor statement(like for browse, ...). But it keeps giving me Err# 16957(for update can not be specified in a Read-Only cursor)

Please help me if you can!

thanks in advanced

Ramin

P.S: I'm using a linkedserver connection to the host-server of the table(subject of above cursor). does it make any difference?

 

[hneal98]

What are you trying to update?

 

[raminriahi]

i want to update the table in cusor, regarding to some specific condition:
update <table-name> set f1=xxx, f2=yyy, f3=zzz
where current of <cursor_name>

thanks,

 

[SQLSister]

Never do this in a cursor! Bad programming practice.
Write a set based update statement

Update table1
Set col 1 = 'test'
col2 = col3*1.10
where col3 > 10

or something simliar. Tell us the where condition and we can be more specific.

 

[raminriahi]

THis is the whole SP :
Declare cursor statement and one sample of updating statement is marked by following sign:
--<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Thanks much in advance ,

Ramin


CREATE proc cc_TransferImport
as
-- Declare local variables to fetch cursor values into
declare @strGroupCodeFrom varchar(4)
declare @strGroupCodeTo varchar(4)
declare @strSKU varchar(12)
declare @intQty int
declare @dteSentTime datetime
declare @dteConfirmedTime datetime
declare @strReturnCode varchar(4)
declare @dteReceivedTime datetime
declare @strTransferKey varchar(10)
declare @strLoc varchar(10)
declare @strLot varchar(10)
declare @strLocationType varchar(10)
declare @intLocQty int
declare @intTransferDetLine int
declare @intInHandQty int
declare @intTempQty int
--<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Declare Repository.SkuTran1 cursor
declare cur_SKUTran1 cursor scroll_locks
for select Group_Code_From, Group_Code_To, SKU, Qty, Sent_Time, Confirmed_Time, Return_Code, Received_Time
from Sql.Repository.dbo.SKUTran1
where (Sent_Time is Null) and (Return_Code<>'9999')
--<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

begin
-- Open cursor
open cur_SKUTran1
-- Fetch cursor for the first time
fetch next from cur_SKUTran1
into @strGroupCodeFrom, @strGroupCodeTo, @strSKU, @intQty, @dteSentTime, @dteConfirmedTime, @strReturnCode, @dteReceivedTime

-- If there's not any Transfers available
if @@fetch_status<>0
begin
close cur_SKUTran1
DEALLOCATE cur_SKUTran1
return
end

-- Generate Exceed.Transfer_Header record
-- Get transferkey & formatting
if exists(select KeyName from Exceed.dbo.NCounter where KeyName = 'transfer')
select @strTransferKey=cast(KeyCount+1 as varchar(10)) from Exceed.dbo.NCounter where KeyName='transfer'
else
select @strTransferKey='1'
if exists(select KeyName from Exceed.dbo.NCounter where KeyName = 'transfer')
update Exceed.dbo.NCounter set KeyCount=KeyCount+1 where KeyName='transfer'
else
insert into Exceed.dbo.NCounter (KeyName, KeyCount) values ('transfer', 1)
set @strTransferKey=replicate('0', 10-len(@strTransferKey)) + @strTransferKey
insert into Exceed.dbo.Transfer
(TransferKey,FromStorerKey, ToStorerKey, Type, Status, Facility)
values (@strTransferKey, @strGroupCodeFrom, @strGroupCodeTo, 'STORER', '0', 'F1')

set @intTransferDetLine=0

-- Fetch Repository.Transfer records in loop until the last one is reached
while @@fetch_status = 0
begin
select @intInHandQty = sum(Qty) from Exceed.dbo.LotXLocXID where (Exceed.dbo.LotXLocXID .SKU=@strSKU) and (Exceed.dbo.LotXLocXID.StorerKey=@strGroupCodeFrom)

-- Not enough in-stock qty
if @intInHandQty<@intQty
begin
--<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Marking the SkuTran1 record as bad record
update Sql.Repository.dbo.SkuTran1 set Return_Code='9999' from Sql.Repository.dbo.SkuTran1 ST1
where current of cur_SKUTran1 (ST1.Group_Code_From=@strGroupCodeFrom) and (ST1.Group_Code_To=@strGroupCodeTo) and (ST1.Received_Time=@dteReceivedTime) and
(ST1.Sku=@strSKU) and (ST1.Qty=@intQty)
--<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
fetch next from cur_SKUTran1
into @strGroupCodeFrom, @strGroupCodeTo, @strSKU, @intQty, @dteSentTime, @dteConfirmedTime, @strReturnCode, @dteReceivedTime
continue
end

-- Lookup for finding locations for transfered SKU in Exceed tables
declare cur_LotxLocxID cursor for
select Exceed.dbo.LotXLocXID.Loc, Exceed.dbo.LotXLocXID.Lot, Exceed.dbo.SkuXLoc.LocationType, Exceed.dbo.LotXLocXID.Qty
from Exceed.dbo.LotXLocXID join Exceed.dbo.SkuXLoc on
(Exceed.dbo.LotXLocXID.StorerKey=Exceed.dbo.SkuXLoc.StorerKey) and (Exceed.dbo.LotXLocXID.SKU=Exceed.dbo.SkuXLoc.SKU)
and (Exceed.dbo.LotXLocXID .Loc=Exceed.dbo.SkuXLoc.Loc)
where (Exceed.dbo.LotXLocXID .SKU=@strSKU) and (Exceed.dbo.LotXLocXID.StorerKey=@strGroupCodeFrom) and (Exceed.dbo.LotXLocXID.Qty<>0)
order by Exceed.dbo.SkuXLoc.LocationType, Exceed.dbo.LotXLocXID.Qty desc

open cur_LotxLocxID
fetch next from cur_LotxLocxID
into @strLoc, @strLot, @strLocationType, @intLocQty

if @@fetch_status<>0
begin
-- Marking the SkuTran1 record as bad record
update Sql.Repository.dbo.SkuTran1 set Return_Code='9999' from Sql.Repository.dbo.SkuTran1 ST1
where ST1.Group_Code_From=@strGroupCodeFrom) and (ST1.Group_Code_To=@strGroupCodeTo) and (ST1.Received_Time=@dteReceivedTime)
end

-- Fetch matches for Transfered SKU in Exceed.LotxLocXID table
while @@fetch_status = 0
begin
if @intQty<=@intLocQty
begin
set @intTransferDetLine=@intTransferDetLine+1
insert into Exceed.dbo.TransferDetail
(TransferKey, TransferLineNumber, FromStorerKey,FromSKU, FromLoc, FromLot, FromQty, FromPackKey, FromUOM,
ToStorerKey, ToSKU, ToLoc, ToLot, ToQty, ToPackKey, ToUOM, Status)
values (@strTransferKey, replicate('0', 5-len(cast(@intTransferDetLine as varchar(5))))+cast(@intTransferDetLine as varchar(5)),
@strGroupCodeFrom, @strSKU, @strLoc, @strLot, @intQty, 'STD', 'EA', @strGroupCodeTo, @strSKU, @strLoc, @strLot, @intQty, 'STD', 'EA', '0')

-- Marking the SkuTran1 record as Exported
update Sql.Repository.dbo.SkuTran1 set Sent_Time=getdate(), Confirmed_Time=getdate() --from Sql.Repository.dbo.SkuTran1 ST1
where (ST1.Sent_Time is Null) and (ST1.Return_Code<>'9999') and (ST1.Sku=@strSKU) and
(ST1.Group_Code_From=@strGroupCodeFrom) and (ST1.Group_Code_To=@strGroupCodeTo) and (ST1.Received_Time=@dteReceivedTime)
break
end
else
begin
set @intTransferDetLine=@intTransferDetLine+1
insert into Exceed.dbo.TransferDetail
(TransferKey, TransferLineNumber, FromStorerKey,FromSKU, FromLoc, FromLot, FromQty, FromPackKey, FromUOM,
ToStorerKey, ToSKU, ToLoc, ToLot, ToQty, ToPackKey, ToUOM, Status)
values (@strTransferKey, replicate('0', 5-len(cast(@intTransferDetLine as varchar(5))))+cast(@intTransferDetLine as varchar(5)),
@strGroupCodeFrom, @strSKU, @strLoc, @strLot, @intLocQty, 'STD', 'EA', @strGroupCodeTo, @strSKU, @strLoc, @strLot, @intLocQty, 'STD', 'EA', '0')

set @intQty=@intQty - @intLocQty
end
fetch next from cur_LotxLocxID
into @strLoc, @strLot, @strLocationType, @intLocQty
end

close cur_LotxLocxID
deallocate cur_LotxLocxID

fetch next from cur_SKUTran1
into @strGroupCodeFrom, @strGroupCodeTo, @strSKU, @intQty, @dteSentTime, @dteConfirmedTime, @strReturnCode, @dteReceivedTime
end

-- Close and deallocate cursor
close cur_SKUTran1
DEALLOCATE cur_SKUTran1
end
GO