数据库存储过程定义的存储.
报错:
select FOrderNum,sum(FOrderQuantity) as [sum of FOrderQuantity],sum(case FGoodName
when 电风扇 then FOrderQuantity else null
End) as [电风扇],sum(case FGoodName
when 毛笔 then FOrderQuantity else null
End) as [毛笔],sum(case FGoodName
when 书 then FOrderQuantity else null
End) as [书],sum(case FGoodName
when 洗衣机 then FOrderQuantity else null
End) as [洗衣机],sum(case FGoodName
when 衣架 then FOrderQuantity else null
End) as [衣架]from[dbo].[T_Customer]group byFOrderNum
我的代码:
ALTER PROCEDURE Usp_Static
@tableName nvarchar(50),
@groupCol nvarchar(50),--分组字段.
@staticCol nvarchar(50),--统计字段.
@showCol nvarchar(50),--表头字段.
@opera nvarchar(50)--聚会函数.
AS
declare @SQL nvarchar(max), @proColumn nvarchar(50)--存储游标执行的列.
execute ('declare curCol cursor for select distinct '+ @showCol+ ' from '+ @tableName+' for read only') --游标.
begin
set nocount on
set @SQL = 'select '+@groupCol+','+@opera+'('+@staticCol+') as ['+@opera+' of '+ @staticCol+']';
open curCol --打开游标.
while(0=0)
Begin
Fetch next from curCol into @proColumn
if(@@fetch_status<>0) break--游标失败.
set @SQL += ','+@opera+'(case '+@showCol+'
when '+@proColumn+' then '+@staticCol+' else null
End) as ['+@proColumn+']'
End
set @SQL += 'from'+ @tableName +'group by'+ @groupCol
execute @SQL
if(@@error <> 0) return @@error --出错.
close curCol
deallocate curCol return 0--释放游标成功则返回0.
end
