USE [pubs]਀䜀伀
/****** Object:  StoredProcedure [dbo].[test_a]    Script Date: 02/20/2008 13:01:15 ******/਀匀䔀吀 䄀一匀䤀开一唀䰀䰀匀 伀一
GO਀匀䔀吀 儀唀伀吀䔀䐀开䤀䐀䔀一吀䤀䘀䤀䔀刀 伀一
GO਀挀爀攀愀琀攀 瀀爀漀挀 嬀搀戀漀崀⸀嬀琀攀猀琀开愀崀 ⠀䀀夀攀愀爀㄀ 渀瘀愀爀挀栀愀爀⠀㐀⤀Ⰰ 䀀夀攀愀爀㈀ 渀瘀愀爀挀栀愀爀⠀㐀⤀⤀ 愀猀
begin਀搀攀挀氀愀爀攀 䀀猀焀氀 渀瘀愀爀挀栀愀爀⠀㐀　　　⤀
set @sql = 'select title_id '਀搀攀挀氀愀爀攀 䀀椀 椀渀琀Ⰰ 䀀樀 椀渀琀Ⰰ 䀀欀 椀渀琀
set @i = convert(int,@Year1)਀猀攀琀 䀀樀 㴀 挀漀渀瘀攀爀琀⠀椀渀琀Ⰰ䀀礀攀愀爀㈀⤀
set @k = @i਀眀栀椀氀攀 䀀欀 㰀㴀 䀀樀 戀攀最椀渀
  set @sql = @sql + ',sum(case year(ord_date) when ' + convert(nvarchar(40),@k) + ' then qty else 0 end) as Y' + convert(nvarchar(40),@k)਀  猀攀琀 䀀欀 㴀 䀀欀 ⬀㄀
end਀
set @sql = @sql + ' from sales group by title_id with rollup'਀攀砀攀挀 ⠀䀀猀焀氀⤀
return @@rowcount-1਀攀渀搀