求储存

--------表结构---------------CREATE TABLE [dbo].[RPT_resultA41](    [SegtID] [int] NULL,    [Segtname] [varchar](30) NULL,    [acctnobe] [varchar](10) NULL,    [acctname] [varchar](50) NULL,    [acctid] [int] NULL,    [acctname2] [varchar](30) NULL,    [Mtd201301] [money] NULL,    [Mtd201302] [money] NULL,    [Mtd201303] [money] NULL,    [Mtd201204] [money] NULL,    [Mtd201205] [money] NULL,    [Mtd201206] [money] NULL,    [Mtd201207] [money] NULL,    [Mtd201208] [money] NULL,    [Mtd201209] [money] NULL,    [Mtd201210] [money] NULL,    [Mtd201211] [money] NULL,    [Mtd201212] [money] NULL,    [Ytd] [money] NULL,    [AvgPerMth] [money] NULL,    [BudgetYtd] [money] NULL,    [BudgetAvgPerMth] [money] NULL,    [VarianceUsd] [money] NULL,    [VariancePercent] [money] NULL,    [LastYrYtd] [money] NULL,    [LastYrAvgPerMth] [money] NULL,    [classify] [int] NULL,    [Subtotal] [int] NULL,    [CompanyID] [int] NULL,    [UserID] [int] NULL,    [DateCreated] [datetime] NULL---------------需求-------------------

---建一个下面的存储,补充as后面的 create proc PRT_41(@Cid varchar,----字段CompanyID@beginyear int,----开始年份(例如:字段'Mtd201204'中的2012)@beginmonth int,----开始月份(例如:字段'Mtd201204'中的04)@endyear int,-----结束年份@ednmonth int)----结束月份as--- 1  实现当输入exec PRT_41 1,2012,2,2012,4时就输出CompanyID=1的2012年2月到4月的数据,----2  除了不在范围的月份,其他字段保留,像SegID,acctname等(月份字段也可以保留,但不能有数据)----3  如果可以,YTD的数是前面所显示的月份数据相加!

IF OBJECT_ID('[PRT_41]',N'P') IS NOT NULL DROP PROC [PRT_41]GO create proc PRT_41(@Cid varchar,----字段CompanyID@beginyear int,----开始年份(例如:字段'Mtd201204'中的2012)@beginmonth int,----开始月份(例如:字段'Mtd201204'中的04)@endyear int,-----结束年份@ednmonth int)----结束月份ASBEGIN     DECLARE @col VARCHAR(1000),@total VARCHAR(1000),@sql VARCHAR(1000)    WHILE @beginmonth<[email protected]        BEGIN             SET @col=ISNULL(@col+',','')+'[Mtd2012'+RIGHT([email protected],2)+']'            SET @total=ISNULL(@total+'+','')+'[Mtd2012'+RIGHT([email protected],2)+']'            SET @[email protected]+1        END     SET @col='[SegtID],[Segtname],[acctnobe],[acctname],[acctid],[acctname2],[email protected]+',[total][email protected]    SET @[email protected]+',[Ytd],[AvgPerMth],[BudgetYtd], [BudgetAvgPerMth], [VarianceUsd],[VariancePercent]'    SET @[email protected]+',[LastYrYtd],[LastYrAvgPerMth],[classify] ,[Subtotal] ,[CompanyID], [UserID] ,[DateCreated]'    SET @sql='SELECT [email protected]+' FROM [RPT_resultA41] WHERE CompanyID='+LTRIM(@Cid)    --SELECT   @sql     EXEC(@sql)    END go exec PRT_41 1,2012,2,2012,4DROP TABLE [RPT_resultA41]DROP PROC [PRT_41]