for my refererence i used:
print 'Starting Pre Order Activation'
go
-- and date >"19 may 2004" and date < "25 may 2004")
-- and date >"25 may 2004" and date < "30 may 2004")
declare cur_ws cursor for
select id , memberid
from member_order
where id in (
select orderid from diary_order
where diaryid in ( select id from diary where eventid = 115 )
)
and statuscode != 'cancelled'
go
begin
declare
@orderid numeric(9, 0),
@memberid numeric(9, 0)
open cur_ws
fetch cur_ws into @orderid, @memberid
while @@sqlstatus = 0
begin
print 'Updating Pre Order %1! member: %2!', @orderid, @memberid
begin tran
-- declare vars
declare @eventid int
declare @diary_title varchar(20)
declare @diary_text varchar(20)
declare @diaryid numeric(9,0)
--- select vars
select @eventid = 116
select @diary_title = 'Activated Pre Orders'
select @diary_text = ''
-- exec it
execute ins_diary_update_notran_sp
1, -- diary type id
@eventid, -- event id
null, -- date (null gets server time)
@diary_title, -- diary title
@diary_text, -- diary text
null, -- service provider id
@orderid, --
@memberid, --
null, -- batch
null, -- order product id
0, -- include record in diary_spr
1, -- include record in diary_order
1, -- include record in member_diary
0, -- do not include record in diary_spr_batch
0, -- do not include record in diary_order_product
@diaryid output
-- error test
if @@error != 0
begin
rollback
print "new diary and workstack for pre odrers failed"
goto theend
end
commit
fetch cur_ws into @orderid, @memberid
end
close cur_ws
deallocate cursor cur_ws
theend:
end
go
Main Topics
Browse All Topics
by: alpmoonPosted on 2004-05-11 at 17:13:38ID: 11045800
Basically it should be like this:
declare recomp_crs cursor for
select id, type
from TABLE T
where country = 'uk'
for read only
go
declare @id int,
@type varchar(10),
@proc_err int
open recomp_crs
fetch recomp_crs into @id, @type
if @@sqlstatus != 0
begin
print "Table is empty!"
end
while @@sqlstatus = 0
begin
exec @proc_err = update_now @id, @type
-- You should have error handling for @proc_err here
fetch recomp_crs into @id, @type
end
close recomp_crs
go