大家帮我看看我写的一存储过程
SELECT Emp_Name, SUM(基本工资) AS 基本工资, SUM(奖金) AS 奖金, SUM(水电费)
AS 水电费
FROM (
SELECT Emp_Name, Money AS 基本工资, 0 AS 奖金, 0 AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 1)
UNION ALL
SELECT Emp_Name, 0 AS 基本工资, Money AS 奖金, 0 AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 2)
UNION ALL
SELECT Emp_Name, 0 AS 基本工资, 0 AS 奖金, Money AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 3)) a
GROUP BY Emp_Name
create procedure P_QueryWages @Start_ID int, @End_ID int
AS
declare @cmd varchar(8000)
set @Cmd = "SELECT Emp_Name, "
declare @Num int
select @Num=count(id) from Items
declare @i int
set @i=1
while @i <@Num
begin
declare @name varchar(100)
select @name=Name from Items where id=@i
set @Cmd = @Cmd + 'SUM( '+ @name + ') AS '+ @name
if (@i <> @Num)
set @Cmd=@Cmd + ', '
end
set @Cmd = @Cmd + 'FROM (SELECT Emp_Name, '
set @i=1
while @i <@Num
begin
declare @j int
set @j=1
while @j <@Num
begin
declare @name1 varchar(100)
select @name1 = Name from Items where id= @j
if(@i==@j)
set @Cmd=@Cmd + 'Money AS '+@name1
else
set @Cmd=@Cmd + '0 AS '+@name1
if(@j <> @Num)
set @Cmd=@Cmd + ', '
end
set @Cmd = @Cmd + ' FROM V_WagesDetail WHERE (Item_id = ' + @i + ') and [id] > = start_id and [id] <= end_id '
if (@i <> @Num)
set @Cmd =@Cmd + 'union all '
end
set @Cmd = @Cmd + ') a GROUP BY Emp_Name '
exec(@Cmd)
[解决办法]
create procedure P_QueryWages @Start_ID int, @End_ID int
AS
declare @cmd varchar(8000)
set @Cmd = 'SELECT Emp_Name, ' --这个地方有错
declare @Num int
select @Num=count(id) from Items
declare @i int
set @i=1
while @i <@Num
begin
declare @name varchar(100)
select @name=Name from Items where id=@i
set @Cmd = @Cmd + ' SUM( '+ @name + ') AS '+ @name
if (@i <> @Num)
set @Cmd=@Cmd + ', '
end
set @Cmd = @Cmd + ' FROM (SELECT Emp_Name, '
set @i=1
while @i <@Num
begin
declare @j int
set @j=1
while @j <@Num
begin
declare @name1 varchar(100)
select @name1 = [Name] from Items where id= @j
if(@i=@j) --这个地方有错
set @Cmd=@Cmd + ' Money AS '+@name1
else
set @Cmd=@Cmd + ' 0 AS '+@name1
if(@j <> @Num)
set @Cmd=@Cmd + ', '
end
set @Cmd = @Cmd + ' FROM V_WagesDetail WHERE (Item_id = ' + @i + ') and [id] > = start_id and [id] <= end_id '
if (@i <> @Num)
set @Cmd =@Cmd + 'union all '
end
set @Cmd = @Cmd + ') a GROUP BY Emp_Name '
exec(@Cmd)
