USE [pubs]਀䜀伀
/****** Object:  StoredProcedure [dbo].[test_a2]    Script Date: 02/20/2008 13:05:49 ******/਀匀䔀吀 䄀一匀䤀开一唀䰀䰀匀 伀一
GO਀匀䔀吀 儀唀伀吀䔀䐀开䤀䐀䔀一吀䤀䘀䤀䔀刀 伀一
GO਀
create proc [dbo].[test_a2] (@Year1 nvarchar(4), @Year2 nvarchar(4),@sum1 int output,@avg1 int output) as਀戀攀最椀渀
declare @sql nvarchar(4000)਀猀攀琀 䀀猀焀氀 㴀 ✀猀攀氀攀挀琀 琀椀琀氀攀开椀搀 ✀
declare @i int, @j int, @k int਀猀攀琀 䀀椀 㴀 挀漀渀瘀攀爀琀⠀椀渀琀Ⰰ䀀夀攀愀爀㄀⤀
set @j = convert(int,@year2)਀猀攀琀 䀀欀 㴀 䀀椀
while @k <= @j begin਀  猀攀琀 䀀猀焀氀 㴀 䀀猀焀氀 ⬀ ✀Ⰰ猀甀洀⠀挀愀猀攀 礀攀愀爀⠀漀爀搀开搀愀琀攀⤀ 眀栀攀渀 ✀ ⬀ 挀漀渀瘀攀爀琀⠀渀瘀愀爀挀栀愀爀⠀㐀　⤀Ⰰ䀀欀⤀ ⬀ ✀ 琀栀攀渀 焀琀礀 攀氀猀攀 　 攀渀搀⤀ 愀猀 夀✀ ⬀ 挀漀渀瘀攀爀琀⠀渀瘀愀爀挀栀愀爀⠀㐀　⤀Ⰰ䀀欀⤀
  set @k = @k +1਀  
end਀
declare @k3 int਀猀攀琀 䀀猀焀氀 㴀 䀀猀焀氀 ⬀ ✀ 昀爀漀洀 猀愀氀攀猀 最爀漀甀瀀 戀礀 琀椀琀氀攀开椀搀✀
exec (@sql)਀猀攀琀 䀀欀㌀ 㴀 䀀䀀爀漀眀挀漀甀渀琀 ⴀ ㄀
select @sum1 = sum(qty), @avg1 = avg(qty) from sales where Year(ord_date) between਀ऀ挀漀渀瘀攀爀琀⠀椀渀琀Ⰰ 䀀礀攀愀爀㄀⤀ 愀渀搀 挀漀渀瘀攀爀琀⠀椀渀琀Ⰰ䀀礀攀愀爀㈀⤀
return @k3਀攀渀搀