来个存储过程，统计一上数据_Sql Server

来个存储过程，统计一下数据。
产品表
===========有两列=========
产品型号添加时间（datetime类型的数据）
AAA 2010-12-10 18:43:31
BBB 2010-05-10 11:43:31
BBB 2010-07-10 15:43:31
AAA 2010-08-10 17:43:31
EEE 2010-08-10 15:43:31
AAA 2010-08-10 18:43:31
EEE 2010-08-10 18:43:31
FFF 2010-06-10 18:43:31
. .
. .
. .

要求一个存储过程，可以输入两个时间参数（起止时间），然后统计这两个时间段内，产品的数量

假如输入起止时间是2010.05和2010.09则输出的结果如下

------解决方案--------------------

SQL code

create proc test (@begin datetime@endtime datetime)asbeginselect  产品型号,  sum(case when convert(varchar(7),添加时间,120)='2010-05' then 数量 else 0 end) as '2010-05',.....from  tbwhere  添加时间 between @begin and @endtimegroup by  产品型号end
------解决方案--------------------
还缺了一个合计，补上。

CREATE PROCEDURE [dbo].[SelectByDate](@begin as DateTime, @end as DateTime)
AS
BEGIN
Declare @bs as varchar(50)
declare @es as varchar(30)
declare @res as varchar(4000)
declare @s as varchar(4000)
declare @temp as DateTime

set @bs = 'sum(case CONVERT(varchar(7),adddate, 120) when '
set @es = ' as '
set @s = ''
set @temp = @begin
while CONVERT(varchar(7), @temp, 120) <= CONVERT(varchar(7), @end, 120)
begin
set @res = @bs + quotename(convert(varchar(7),@temp, 120), '''') + ' then 1 else 0 end)' + @es + quotename(convert(varchar(7),@temp, 120), '''')
set @s = @s + ',' + @res
set @temp = dateadd(month, 1, @temp)
end
set @res = 'select productno' + @s + ', count(productno) as 总计 from productinfo where adddate between ' + quotename(convert(varchar,@begin), '''') + ' and ' + quotename(convert(varchar, @end), '''') + ' group by productno'
set @s = @res + ' union ' + 'select ''合计''' + @s + ', count(productno) as 总计 from productinfo where adddate between ' + quotename(convert(varchar,@begin), '''') + ' and ' + quotename(convert(varchar, @end), '''')
exec(@s)
END