sql 游标的存储过程的使用

CREATE PROCEDURE App_Demo_zspExecuteHousingbyDemolitionId222222
(
  @pi_Action VARCHAR(100),
  @pi_HousingId1 varchar(50),
     @pi_HousingId2 varchar(50),
  @pi_HousingId3 varchar(50),
     @pi_DemolitionId varchar(50), 
     @po_Status int output,
     @po_RetValue varchar(200) output
)
AS
declare @pt_HousingId varchar(100)
 IF @pi_Action = 'modify'
 BEGIN        

--临时表的定义
declare @pt_tbHousing table(HousingId varchar(100))
insert into @pt_tbHousing
(
HousingId
)
select HousingId from Demo_HousingInfo where DemolitionId=@pi_DemolitionId
--定义游标

 DECLARE curTmpHousingCursor SCROLL CURSOR FOR
   (
     SELECT
     HousingId
     FROM @pt_tbHousing
   )
   OPEN curTmpHousingCursor
   IF @@CURSOR_ROWS > 0
   BEGIN
    BEGIN TRANSACTION
   
    FETCH NEXT FROM  curTmpHousingCursor INTO
    @pt_HousingId
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
     update Demo_HousingInfo set DemolitionId='' where HousingId=@pt_HousingId
     FETCH NEXT FROM  curTmpHousingCursor INTO
    @pt_HousingId    
    END
   END  
  CLOSE curTmpHousingCursor
  DEALLOCATE curTmpHousingCursor
  END
update Demo_HousingInfo set
         DemolitionId=@pi_DemolitionId
         where HousingId=@pi_HousingId1
update Demo_HousingInfo set
         DemolitionId=@pi_DemolitionId
         where HousingId=@pi_HousingId2
update Demo_HousingInfo set
         DemolitionId=@pi_DemolitionId
         where HousingId=@pi_HousingId3 
if @@error <> 0
begin
rollback tran
 set @po_status = 900
 set @po_RetValue = '数据执行失败'
end
 else
begin
commit tran
       set @po_status = 103
       set @po_RetValue='选房成功'
end