SQL Server 数据库升级脚本步骤

代码如下:

--升级GPOS1.0到GPOS1.1数据库的升级脚本 2013-7-4
USE [GPOSDB]
GO
------------------删除所有存储过程-------------------
--select * from sys.procedures

declare @sql varchar(4000)set @sql=''select @sql=@sql+'drop proc '+name+'; ' from sys.procedures--print @sqlexec(@sql)

--------------------------------在[CT_OuterCard]表添加6个字段-------------------------------ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransCurrCount] INT NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransCurrCount] DEFAULT ((0))ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransType] [int] CONSTRAINT [DF_CT_OuterCard_I_LimitTransType] DEFAULT ((0))ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransTotal] DEFAULT ((0))ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransCurrTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransCurrTotal] DEFAULT ((0))ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitCarNo] [int] NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitCarNo] DEFAULT ((0))ALTER TABLE [dbo].[CT_OuterCard] ADD [D_LimitDate] [datetime] NOT NULL CONSTRAINT [DF_CT_OuterCard_D_LimitDate] DEFAULT (getdate())----------------------------------------------------------------------------------------------------------------把刚才在新建查询窗口里生成的存储过程脚本粘贴到下面---------------------------创建GPOS1.1的所有存储过程---------------------------------------------USE [GPOSDB]GO/****** 对象: StoredProcedure [dbo].[Report_GreaserSaleStat] 脚本日期: 07/04/2013 13:27:09 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO

CREATE PROC [dbo].[Report_GreaserSaleStat]@StartDate datetime,@EndDate datetime,@Action int --0为交易记录，1为班次记录

insert into #tmpCardAmoutStat ( VC_OC_CardNO, set @i=@i+1 end

truncate table #tmpCards insert into #tmpCards(VC_OC_CardNO) select VC_OC_CardNO from CT_OuterCard where isnull(VC_OC_Company,'')='' set @j=1 select @cardcount=count(*) from #tmpCards while @j<=@cardcount begin select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where IndexId=@j insert into #tmpCardAmoutStat ( VC_OC_CardNO,

insert into #tmpCardAmoutStat ( VC_OC_CardNO, CompanyName, VC_OC_UserName, StartAmount, FillMoney, ConsumeSumVol, ConsumeMoney, SumConsumeSumVol, SumConsumeMoney, SumFillMoney

) select null, null, '客户卡小计', sum(StartAmount), sum(FillMoney), sum(ConsumeSumVol), truncate table #tmpCards insert into #tmpCards(VC_OC_CardNO) select VC_IC_CardNO from CT_InhouseCard where isnull(VC_IC_CardNO,'')<>'' set @j=1 select @cardcount=count(*) from #tmpCards while @j<=@cardcount begin select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where IndexId=@j insert into #tmpCardAmoutStat ( VC_OC_CardNO, CompanyName, VC_OC_UserName, StartAmount, FillMoney, ConsumeSumVol, ConsumeMoney, SumConsumeSumVol, SumConsumeMoney, SumFillMoney

) select @VC_OC_CardNO, '员工卡', isnull((select VC_IC_UserName from CT_InhouseCard where VC_IC_CardNO=@VC_OC_CardNO),''), isnull((select top 1 DE_FD_Amount from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime<=@StartDate) order by D_FD_DateTime desc),0), isnull((select sum(DE_A_AppendAmount) from CT_Append where VC_A_CardNO=@VC_OC_CardNO and (D_A_AppendDateTime between @StartDate and @EndDate)),0), isnull((select sum(DE_FD_Volume) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0), isnull((select sum(DE_FD_Amount) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0), isnull((select sum(DE_FD_Volume) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0), isnull((select sum(DE_FD_Amount) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0), isnull((select sum(DE_A_AppendAmount) from CT_Append where VC_A_CardNO=@VC_OC_CardNO),0) set @j=@j+1 end

insert into #tmpCardAmoutStat ( VC_OC_CardNO, CompanyName, VC_OC_UserName, StartAmount, FillMoney, ConsumeSumVol, ConsumeMoney, SumConsumeSumVol, SumConsumeMoney, SumFillMoney

) select null, null, '员工卡小计', sum(StartAmount), sum(FillMoney), sum(ConsumeSumVol), sum(ConsumeMoney), sum(SumConsumeSumVol), sum(SumConsumeMoney), sum(SumFillMoney) from #tmpCardAmoutStat where CompanyName='员工卡' ---计算员工卡汇总结束--- end ----计算总汇总开始--- insert into #tmpCardAmoutStat ( VC_OC_CardNO, CompanyName, VC_OC_UserName, StartAmount, FillMoney, ConsumeSumVol, ConsumeMoney, SumConsumeSumVol, SumConsumeMoney, SumFillMoney

) select null, null, '总计', sum(StartAmount), sum(FillMoney), sum(ConsumeSumVol), sum(ConsumeMoney), sum(SumConsumeSumVol), sum(SumConsumeMoney), sum(SumFillMoney) from #tmpCardAmoutStat where (VC_OC_UserName='客户卡小计' or VC_OC_UserName='员工卡小计') and VC_OC_CardNO is null update #tmpCardAmoutStat set EndAmount=StartAmount+FillMoney-ConsumeMoney ---计算总汇总结束--- select * from #tmpCardAmoutStat

drop table #tmpCards drop table #tmpCompanys drop table #tmpCardAmoutStatGO

--其他存储过程省略。。。。。。。。。。。