【转载】MSSQL 动态sql使用游标

MS-SQL 动态sql使用游标

CREATE PROCEDURE [dbo].[P_MissiveRights]
 -- Add the parameters for the stored procedure here
 @UserCode varchar(50)
AS
BEGIN
 SET NOCOUNT ON;
 declare @UserBiaoShi int   

 declare @id int   
 declare @sql0 nvarchar(4000)  --sql字符串 
 declare @sqltmp nvarchar(4000)      --临时变量

 CREATE Table #RightsTable (
        HangBiaoShi int,
        ZhongLei int       
    )
 set @sqltmp = 'select @x=BiaoShi from MiMa where User_Code='''+@UserCode+''''
 EXECUTE sp_executesql @sqltmp, N'@x int output',@x=@UserBiaoShi output

 if @UserBiaoShi>0
  begin

   declare master_cursor CURSOR FOR
   select id from table0   
   open master_cursor

   FETCH NEXT FROM master_cursor into @id

   while @@fetch_status = 0
   BEGIN     
        set @sql0='declare senc_cursor cursor for  Select * from Table1 '
    --declare senc_cursor CURSOR For
    exec(@sql0)
    open senc_cursor
    FETCH NEXT FROM senc_cursor  into @HangBiaoShi,@WenJianZhongLei
    while @@fetch_status = 0
     begin

      insert into #RightsTable values (@HangBiaoShi,@WenJianZhongLei)

      FETCH NEXT FROM senc_cursor  into @HangBiaoShi,@WenJianZhongLei
     end
    CLOSE senc_cursor
    DEALLOCATE senc_cursor
   FETCH NEXT FROM master_cursor into @id

   END
   CLOSE master_cursor
   DEALLOCATE master_cursor
  end

 SELECT * FROM #RightsTable
END