ÿþUSE [pubs]
GO
/****** Object:  StoredProcedure [dbo].[test_a]    Script Date: 02/20/2008 13:01:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[test_a] (@Year1 nvarchar(4), @Year2 nvarchar(4)) 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

set @sql = @sql + ' from sales group by title_id with rollup'
exec (@sql)
return @@rowcount-1
end