SQLserver 存储过程游标使用

ALTER PROCEDURE [dbo].[p_DeleteStretchData]

  @BridgeName AS varchar(200) ,

  @BeamNo AS varchar(200)

AS

BEGIN

    declare @BeamId varchar(50)

    declare @StretchId varchar(50)

    declare @stretch_cursor cursor  -- 声明游标变量

    select @BeamId=BeamId from Beam_Info where BridgeName = ltrim(rtrim(@BridgeName)) and BeamNumber = ltrim(rtrim(@BeamNo))

    set @stretch_cursor=cursor for select ID from XYX_Stretch where BeamId = @BeamId  -- 设置游标

    open @stretch_cursor

    fetch next from @stretch_cursor into @StretchId -- 设值

                -- 循环读取

        while @@fetch_status=0

            begin

                -- 删除过程数据

                delete from Process where StretchId = @StretchId

                -- 删除基础数据

                delete from Stretch where ID = @StretchId

                -- 下一条

                fetch next from @stretch_cursor into @StretchId

            end

    close @stretch_cursor

    deallocate @stretch_cursor

    -- 删除状态数据

    delete from Status where BeamId = @BeamId

    -- 删除基础数据

    delete from Beam where BeamId = @BeamId

END