如何把SQL2000存储过程改为Mysql支持的存储过程?
SQL code
create procedure st_CrossTableByRingSame @Row varchar(50), @Col Varchar(50), @OrderBy varchar(50), /*用于列排序*/ @Sum Varchar(100), @SQL_Condition Varchar(8000) with ENCRYPTION as declare @Productno varchar(100) --declare @qty float 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 -- if @OrderBy <>'' set @sql3=' insert into #tmp select distinct scol,serial from ('+@SQL1+' ) as pp order by Serial' -- else -- set @sql3=' insert into #tmp select distinct scol from ('+@SQL1+' ) as pp' print @sql3 exec (@sql3) --print '@sql3ok' 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'+convert(varchar(2),@i) 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 --print 'OK' 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 ') --print @SQL2 exec (@SQL2 + ' select * from #tempsum'+ -- ' select convert(varchar(5),DL.DayTimeValue,108)as CollectionTime,B.* from tbDayMinuteList DL left join #tempsum b on b.SRow=convert(varchar(5),DL.DayTimeValue,108) order by DL.DayTimeValue '+ ' drop table #tempsum' )
作者: gisslee 发布时间: 2011-05-07
建议楼主先自己参考MYSQL手册中的存储过程语法和例子改一下,
如果自己改的代码仍有问题,则把你的代码和系统提示的错误信息一同贴出,然后大家可以帮你分析学习。
如果自己改的代码仍有问题,则把你的代码和系统提示的错误信息一同贴出,然后大家可以帮你分析学习。
作者: ACMAIN_CHM 发布时间: 2011-05-07
这。。 你还是自己动手吧 以后也不求人
作者: rucypli 发布时间: 2011-05-07