如何把下面SQL2000存储过程改为Mysql支持的存储过程？

[Diving]

如何把下面SQL2000存储过程改为Mysql支持的存储过程？
昨日已发一帖，今日因出差没时间想，各位贴个代码，急用。。。。

create procedure  st_test2011
   @Row varchar(50),
   @Col Varchar(50),
   @OrderBy  varchar(50),  /*用于列排序*/
   @Sum Varchar(100),
   @SQL_Condition  Varchar(8000)

as
   
   declare @Productno varchar(100)
   declare @qty  decimal(18,2)
   declare @s  varchar(2000)
   declare @s_sum  varchar(200)   
   declare @i int
   declare @SQL1  varchar(5000)
   declare @SQL2  varchar(5000)
   declare @SQL3  varchar(5000)  

   set @SQL1='select '+@row+ ' as srow,'+@col+' as scol ,'+'SUM('+@sum+') AS Qty ,'+@OrderBy+' as serial '+ @SQL_Condition 

   select convert(varchar(200),123) as scol ,convert(varchar(100),123) as serial
   into #tmp 
   from tbDivision
   where 0=1
 

   set @sql3=' insert into #tmp  select distinct scol,serial  from ('+@SQL1+' ) as pp order by Serial'


   print @sql3 
   exec (@sql3)


   set @i=0
   set @s_sum=''
   DECLARE MyCursor CURSOR FOR
   select scol from #tmp
   OPEN MyCursor
   FETCH NEXT FROM MyCursor
   INTO  @Productno

   WHILE @@FETCH_STATUS = 0
     BEGIN
       set @i=@i+1
       if @i=1
        begin  
           set @s=' sum(case p.scol when '''+@productno+''' then p.Qty else 0 end) as '+'Q'+@productno
           set @s_sum='P1.Q'+@productno
        end
       else
        begin
           set @s=@s+' ,sum(case p.scol when '''+@productno+''' then p.Qty else 0 end) as '+'Q'+@productno
           set @s_sum=@s_sum+'+P1.Q'+@productno
        end 
      FETCH NEXT FROM MyCursor INTO  @Productno
     END
   CLOSE MyCursor
   DEALLOCATE MyCursor


   set @SQL2=(' SELECT P1.*, '+@s_sum+' AS YearTotal'+
              ' into #tempsum FROM (SELECT P.srow,'+@s+
              ' FROM ('+ @SQL1+') AS P'+
              ' GROUP BY P.srow) AS P1 order by p1.srow ') 

[zuoxingyu]

可以外包出去了。

[rucypli]

lz你好懒

[ACMAIN_CHM]

建议自己先参考手册中的例子和语法进行修改，有什么具体问题再贴出来大家一起讨论。

[tianyaofande]

楼主是标题党！

[wwwwb]

自己动手做一下吧，具体问题再咨询

[gungod]

改这个太累了，不想改，
1.建立临时表循环插入数据，
select convert(varchar(200),123) as scol ,convert(varchar(100),123) as serial
into #tmp
from tbDivision
2.游标操作不一致须修改
WHILE @@FETCH_STATUS = 0
BEGIN
3.IF语法，结束符不同
if 。。then
end if;

delimiter //
。。。
delimter ;

[Diving]

begin

   declare Productno varchar(100);


   declare s  varchar(8000);
   declare s_sum  varchar(8000);
   declare Cursori int;
   declare SQL1  varchar(5000);
   declare SQL3  varchar(8000);
   declare SQL2  varchar(12000)  ;

   --  声明游标
    DECLARE MyCursor CURSOR FOR   select scol from  tmp;

 --  声明游标结束变量
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cursori=1;

 --  删除临时表
 DROP table IF EXISTS tmp;


--  创建临时表
 CREATE TEMPORARY TABLE tmp (
scol VARCHAR(100) ,
serials VARCHAR(100)
);
        set SQL1=concat('select ',inRow, ' as srow,',col,' as scol ,','SUM(',sum,') AS Qty ,',OrderBy,' as serials ', SQL_Condition) ;

       set sql3=concat(' insert into tmp  select distinct scol,serials  from (',SQL1,' ) as pp order by Serials');

    --  动态SQL变量声明
       SET @s1 =SQL3;

        PREPARE stmt2 FROM  @s1  ;
--  将数据写入临时表，用于计算列的个数
        EXECUTE stmt2   ;

        deallocate  PREPARE  stmt2;

 SET Cursori=0;
 --  设置游标变量初始值
  OPEN MyCursor   ;
--  打开游标
      FETCH     MyCursor   INTO  Productno ;
      if ( Cursori<>1  ) then
         if (Productno is not null)  then
        set s=concat(' sum(case p.scol when '',  (productno),' '  then p.Qty else 0 end        ) as  Q',  (productno));
        set s_sum=concat('   P1.Q',  (productno));
        end if;
        end if  ;
 --  组装交叉表查询字符串
 while (  Cursori<>1    ) do

          FETCH     MyCursor   INTO  Productno ;
          if (Productno is not null)  then
       set s=concat(s,' ,sum(case p.scol when '' ,  (productno),'' then p.Qty else 0 end   ) as   Q',  (productno));
    set s_sum=concat (s_sum,' + P1.Q',  (productno));
 end if;
    set  Productno=null;
end while;


 CLOSE MyCursor;
 -- 关闭游标

 set SQL2=concat(' SELECT P1.*, (',s_sum,') AS YearTotal   FROM (SELECT P.srow,',s, ' FROM (',SQL1,')     P    GROUP BY P.srow)    P1 order by p1.srow ') ;
--  执行查询
  set @s2=SQL2;

 PREPARE stmt3 FROM  @s2  ;

  EXECUTE stmt3   ;

  deallocate  PREPARE  stmt3;
 --  删除临时表
   drop table tmp;




end

问题解决，晚贴上来了