ÿþUSE [pubs]
GO
/****** Object:  StoredProcedure [dbo].[test_a2]    Script Date: 02/20/2008 13:05:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[test_a2] (@Year1 nvarchar(4), @Year2 nvarchar(4),@sum1 int output,@avg1 int output) as
begin
declare @sql nvarchar(4000)
set @sql = 'select title_id '
declare @i int, @j int, @k int
set @i = convert(int,@Year1)
set @j = convert(int,@year2)
set @k = @i
while @k <= @j begin
  set @sql = @sql + ',sum(case year(ord_date) when ' + convert(nvarchar(40),@k) + ' then qty else 0 end) as Y' + convert(nvarchar(40),@k)
  set @k = @k +1
  
end

declare @k3 int
set @sql = @sql + ' from sales group by title_id'
exec (@sql)
set @k3 = @@rowcount - 1
select @sum1 = sum(qty), @avg1 = avg(qty) from sales where Year(ord_date) between
	convert(int, @year1) and convert(int,@year2)
return @k3
end