create_proc.sql

来自「是分布式粮库程序,是采用Delphi实现的」· SQL 代码 · 共 48 行

drop procedure Create_proc;
create procedure Create_proc @RptID char(10)
as
declare @Curr_RptID char(10)
declare @Drop_SQL   nvarchar(100)
declare @SQL_String nvarchar(4000)
declare @Proc_Name  char(20)
begin
--游标 得到报表代码 
 declare Get_RptID cursor for
    select RptID from RptName where RptID like Rtrim(@RptID)+'%'
 open Get_RptID
 FETCH NEXT FROM Get_RptID into @Curr_RptID
 WHILE @@FETCH_STATUS = 0
 begin
 
--游标  得到SQL文本和存储过程名
 declare Get_Proc Cursor for
   select FormulaSQL,ProcName from RptCell where RptID=@Curr_RptID
 open  Get_Proc
 FETCH NEXT FROM Get_Proc into @SQL_String,@Proc_Name
 while  @@FETCH_STATUS = 0
 begin
   if exists(select * from sysobjects where name=@Proc_name)
     begin
       select @Drop_SQL='drop Procedure '+@Proc_Name
       execute  sp_executesql @Drop_SQL
     end
    insert dual values(@SQL_String)
     execute sp_executesql @SQL_String 
   FETCH NEXT FROM Get_Proc into @SQL_String,@Proc_Name
 end
--释放游标Get_Proc
 close Get_Proc
 deallocate Get_Proc 

 FETCH NEXT FROM Get_RptID into @Curr_RptID
 end

--释放游标Get_RptID
 close Get_RptID
 DealLocate Get_RptID  
    
     

end
;