sql server嵌套游标

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

--------把模組編號和內膜資料編號插入到一個表中

alter PROCEDURE [dbo].[mold_cmbo_inst]

--declare

@mold_id varchar(20)

--select @mold_id='test000000'

 AS

BEGIN

declare @Mold_cmbo_id varchar(50),@Mold_inst_id varchar(50)

DECLARE mold_cursor  CURSOR FOR

       select Mold_cmbo_id from MOLD_CMBO (nolock) where Mold_id=@mold_id

open mold_cursor     

   FETCH NEXT FROM mold_cursor INTO  @Mold_cmbo_id

   WHILE @@FETCH_STATUS=0

       begin

             DECLARE inst_cursor  CURSOR FOR

                        select Mold_inst_id from MOLD_INST (nolock) where Mold_id=@mold_id

                open inst_cursor                       

                FETCH NEXT FROM inst_cursor INTO  @Mold_inst_id

                WHILE @@FETCH_STATUS=0

                begin                           

                      if not exists(select 1 from mold_cins (nolock) where Mold_cmbo_id=@mold_cmbo_id and Mold_inst_id=@Mold_inst_id)

                      begin

                           insert into mold_cins(Mold_cmbo_id,Mold_inst_id)values(@mold_cmbo_id,@Mold_inst_id)

                      end

                       FETCH NEXT FROM inst_cursor INTO  @Mold_inst_id

                     end

               

                 CLOSE inst_cursor

                 DEALLOCATE inst_cursor

              FETCH NEXT FROM mold_cursor INTO  @Mold_cmbo_id

       END

  

CLOSE mold_cursor

DEALLOCATE mold_cursor

end