游标 存储过程

将原来联系人的名字和性别导入到接听秘书电话方式的留言里面.sql

create proc cmantoleave_mess
as

declare @oid int,@osex varchar(50),@oman varchar(50),@st int

declare lecur cursor for select id, cman 联系人,sex 性别, STELFType from list
where STELFType<>2

open lecur

fetch next from lecur into @oid,@oman,@osex,@st

while(@@fetch_status = 0)
begin

        Update list set STELFType = 1,leave_mess_person=@oman,leave_mess_sex=@osex   where id=@oid
        fetch next from lecur into @oid,@oman,@osex,@st

end
close lecur
deallocate lecur

go

execute cmantoleave_mess

可以使用WHERE   CURRENT   OF   游标名   来更新游标的当前行

create proc editshoplist_mishufenji    --修改已经暂停了的公司列表里面的 秘书分机信息
as

declare @new_STELFType int,@new_leave_mess_beizhu varchar(500)

set @new_STELFType=1

set @new_leave_mess_beizhu='该公司已经不使用我们服务，如有人来电就说无该分机号，或者说打错。'

declare cur_editshoplist_mishufenji cursor for select stelftype,leave_mess_beizhu from list where ATDATEState ='5'
--declare lecur cursor for select id, cman 联系人,sex 性别, STELFType from list

open cur_editshoplist_mishufenji

fetch next from cur_editshoplist_mishufenji

while @@fetch_status = 0
begin

    update list set stelftype=@new_STELFType,leave_mess_beizhu=@new_leave_mess_beizhu where current of cur_editshoplist_mishufenji
    fetch next from cur_editshoplist_mishufenji

end
close cur_editshoplist_mishufenji
deallocate cur_editshoplist_mishufenji

go

exec editshoplist_mishufenji