存储过程备忘（自用）

USE [BMSAccess]
GO


CREATE PROCEDURE [dbo].[QD_Table03] 
--QD_Table03:表示工作联系单的第三项 表格
AS
BEGIN
    DECLARE @TableName varchar(50)
    DECLARE @TableNameYear varchar(50)
DECLARE @Sql nvarchar(1000)
DECLARE @Month varchar(2),@Day varchar(2)
DECLARE @Num int
    declare @Today datetime
set @Today= (select DATEADD(DAY,0,CONVERT(varchar(8),GETDATE(),112)))
set @Num=MONTH(@Today)
if(@Num<10)
set @Month='0' + CAST(@Num as varchar(1))
else
   set @Month=CAST(@Num as varchar(2))
   
set @Num=DAY(@Today)

if(@Num<10)
set @Day='0' + CAST(@Num as varchar(1))
else
   set @Day=CAST(@Num as varchar(2))

    set @TableName='QD_TicketsCheckNew' + CAST(Year(@Today) AS varchar(4)) + @Month + @Day 
    Set @TableNameYear = 'QD_TicketsCheckYearNew' +  CAST(Year(@Today) AS varchar(4)) 
    if exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
        BEGIN
Set @Sql =  'select '+
' (isnull(PiaoJian,0)+ isnull(ZhengJian,0)+isnull(ShouJian,0)+isnull(PiaoJianYear,0)+isnull(ZhengJianYear,0)+isnull(ShouJianYear,0)) ZongHe,'+
' (isnull(PiaoJian,0) + isnull(PiaoJianYear,0)) PiaoJianHe,'+
' (isnull(ZhengJian,0) + isnull(ZhengJianYear,0)) ZhengJianHe,'+
' (isnull(ShouJian,0) + isnull(ShouJianYear,0)) ShouJianHe'+
' from'+
' ('+
'select '+
'(select SUM(value) PiaoJian from '+@TableName+' where TicketType=0) PiaoJian,'+
'(select SUM(value) ZhengJian from '+@TableName+' where TicketType=39) ZhengJian,'+
'(select SUM(value) ShouJian from '+@TableName+' where TicketType=1) ShouJian,'+
'(select sum(Value) PiaoJianYear from '+@TableNameYear+') PiaoJianYear,'+
'(select sum(ZJTotalNum) ZhengJianYear from '+@TableNameYear+') ZhengJianYear,'+
'(select sum(SJTotalNum) ShouJianYear from '+@TableNameYear+') ShouJianYear'+
' ) t'
EXEC sp_executesql @Sql, N'@TableName varchar(50),@TableNameYear varchar(50)', @TableName,@TableNameYear
END 
end 

GO

————————————————————————————————————————————————————————————

Alter PROCEDURE [dbo].[QD_Table04] 
--QD_Table04:表示工作联系单的第四项 表格
AS
BEGIN
    DECLARE @TableName varchar(50)
DECLARE @Sql nvarchar(1000)
DECLARE @Month varchar(2),@Day varchar(2)
DECLARE @Num int
    declare @Today datetime
set @Today= (select DATEADD(DAY,0,CONVERT(varchar(8),GETDATE(),112)))
set @Num=MONTH(@Today)
if(@Num<10)
set @Month='0' + CAST(@Num as varchar(1))
else
   set @Month=CAST(@Num as varchar(2))
   
set @Num=DAY(@Today)

if(@Num<10)
set @Day='0' + CAST(@Num as varchar(1))
else
   set @Day=CAST(@Num as varchar(2))

    set @TableName='QD_TicketsCheckNew' + CAST(Year(@Today) AS varchar(4)) + @Month + @Day 
    
    if exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
        BEGIN
Set @Sql =  'select '+
'(isnull(PiaoJian,0)+ isnull(ZhengJian,0)+isnull(ShouJian,0)) ZongHe,PiaoJian,ZhengJian,ShouJian '+
'from'+
'('+
'select '+
'(select SUM(value) PiaoJian from '+@TableName+' where TicketType=0) PiaoJian,'+
'(select SUM(value) ZhengJian from '+@TableName+' where TicketType=39) ZhengJian,'+
'(select SUM(value) ShouJian from '+@TableName+' where TicketType=1) ShouJian'+
') t '
EXEC sp_executesql @Sql, N'@TableName varchar(50)', @TableName
END 
end 

————————————————————————————————————————————————————————

USE [BMSAccess]
GO


CREATE PROCEDURE [dbo].[QD_Table05] 
--QD_Table05:表示工作联系单的第五项 表格
AS
BEGIN
    DECLARE @TableName varchar(50)
DECLARE @Sql nvarchar(1000)
DECLARE @Month varchar(2),@Day varchar(2)
DECLARE @Num int
    declare @Today datetime
set @Today= (select DATEADD(DAY,0,CONVERT(varchar(8),GETDATE(),112)))
set @Num=MONTH(@Today)
if(@Num<10)
set @Month='0' + CAST(@Num as varchar(1))
else
   set @Month=CAST(@Num as varchar(2))
   
set @Num=DAY(@Today)

if(@Num<10)
set @Day='0' + CAST(@Num as varchar(1))
else
   set @Day=CAST(@Num as varchar(2))

    set @TableName='QD_TicketsCheckNew' + CAST(Year(@Today) AS varchar(4)) + @Month + @Day 
    
    if exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
        BEGIN
Set @Sql =  'select '+
' t1.GroupName,(isnull(t1.PiaoJian,0)+ isnull(t1.ZhengJian,0)+isnull(t1.ShouJian,0)) ZongHe,isnull(PiaoJian,0) PiaoJian,isnull(ZhengJian,0) ZhengJian,isnull(ShouJian,0) ShouJian'+
' from'+
' ('+
' select '+
' t.GroupID,t.GroupName,t.Port,'+
' (select SUM(value) PiaoJian from '+@TableName+' where TicketType=0 and Port=t.Port) PiaoJian,'+
' (select SUM(value) ZhengJian from '+@TableName+' where TicketType=39 and Port=t.Port) ZhengJian,'+
' (select SUM(value) ShouJian from '+@TableName+' where TicketType=1 and Port=t.Port) ShouJian'+
' from'+
' ('+
' select '+
'  GroupID,GroupName,'+
' isnull((select Port from QD_TicketPort where GroupID=QD_CameraGroup.GroupID),0) Port'+
' from '+
' QD_CameraGroup '+
' where TypeID=1 and IsScreen=1 and IsMum=2'+
' ) t'+
') t1'

EXEC sp_executesql @Sql, N'@TableName varchar(50)', @TableName
END 
end 


GO
——————————————————————————————————————————————————————————————

USE [BMSAccess]
GO


CREATE PROCEDURE [dbo].[QD_SaveDayDataNew] 
AS
declare @CheckDate date,@Value int
BEGIN
    DECLARE @TableName varchar(50)
    DeCLARE @TableNameToday varchar(50)
DECLARE @Sql nvarchar(1000)
DECLARE @Sql01 nvarchar(1000)
DECLARE @Sql02 nvarchar(1000)
DECLARE @Month varchar(2),@Day varchar(2)
DECLARE @Num int
    declare @Today datetime
set @Today= (select DATEADD(DAY,0,CONVERT(varchar(8),GETDATE(),112)))
set @Num=MONTH(@Today)
if(@Num<10)
set @Month='0' + CAST(@Num as varchar(1))
else
   set @Month=CAST(@Num as varchar(2))
set @Num=DAY(@Today)
if(@Num<10)
set @Day='0' + CAST(@Num as varchar(1))
else
   set @Day=CAST(@Num as varchar(2))


    Set @TableName = 'QD_TicketsCheckYearNew' +  CAST(Year(@Today) AS varchar(4)) 
    set @TableNameToday='QD_TicketsCheckNew' + CAST(Year(@Today) AS varchar(4)) + @Month + @Day 
    
    if not exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
        BEGIN
Set @Sql = 'CREATE TABLE ' + @TableName + 
'(ID int IDENTITY (1, 1) PRIMARY KEY, 
 CheckDate datetime NOT NULL, 
 Value int default 0,
 ZJTotalNum int default 0,
 SJTotalNum int default 0
)' 
EXEC sp_executesql @Sql, N'@TableName varchar(50)', @TableName
END 
--set @Sql='select SUM(Value) from ' +@TableNameToday
--create table #T(TTD int)
--insert into #T exec(@Sql)
--set @Value=(select TTD from #T)
--drop table #T
 
--set @Sql= 'select @p=SUM(Value) from ' +@TableNameToday + ''
--exec sp_executesql @Sql,N'@p int output',@p = @Value output
----select @Value
create table #TempTable(iHour int)
set @Sql='insert into #TempTable(iHour) select datepart(hour,CheckTime) from '+@TableNameToday+' order by CheckTime '
exec(@Sql)
declare @hour varchar(10)
declare @CheckTime varchar(32),@dCheckDate datetime
declare @SumValue int,@SumValue01 int,@SumValue02 int,@iHour int,@sHour varchar(10)
declare curHourFee cursor for
select distinct iHour from #TempTable order by iHour
open curHourFee
 Fetch next from curHourFee into @hour
 while(@@FETCH_STATUS=0)    
 begin 
 set @iHour=CONVERT(int,@hour)
 set @iHour=@iHour+1
 set @sHour=CONVERT(varchar(10),@iHour)
 set @CheckDate = @Today
 select @CheckTime= RTRIM( CONVERT(varchar, @CheckDate))+' '+LTRIM(str(@sHour)+':00:00')
 select @dCheckDate=CONVERT(datetime,@CheckTime,120)
 --select @sHour=LTRIM(@hour)
 --select @hour
 --开园累计票检 TicketType=0
 set @Sql='select @out=SUM(Value) from '+@TableNameToday+' where  TicketType=0 and DATEPART(HOUR,CheckTime)=CONVERT(int,@p)'
 exec sp_executesql @Sql,N'@p int,@out int out',@p=@hour,@out=@SumValue out
 --select @SumValue
 --开园累计证检 ZJTotalNum TicketType=39
 set @Sql01='select @out01=SUM(Value) from '+@TableNameToday+' where TicketType=39 and DATEPART(HOUR,CheckTime)=CONVERT(int,@p01)'
 exec sp_executesql @Sql01,N'@p01 int,@out01 int out',@p01=@hour,@out01=@SumValue01 out
 --开园累计手检 SJTotalNum TicketType=1
 set @Sql02='select @out02=SUM(Value) from '+@TableNameToday+' where  TicketType=1 and DATEPART(HOUR,CheckTime)=CONVERT(int,@p02)'
 exec sp_executesql @Sql02,N'@p02 int,@out02 int out',@p02=@hour,@out02=@SumValue02 out
 
         set @Sql='insert into '+@TableName+'(CheckDate,Value,ZJTotalNum,SJTotalNum) values(@q,@v,@v01,@v02) ' 
         
         if @SumValue is null set @SumValue=0
         if @SumValue01 is null set @SumValue01=0
         if @SumValue02 is null set @SumValue02=0
         exec sp_executesql @Sql,N'@q datetime,@v int,@v01 int,@v02 int',@dCheckDate ,@SumValue,@SumValue01,@SumValue02
         
 Fetch next from curHourFee into @hour
 end
close curHourFee
deallocate curHourFee
 
--set @Sql='select * from '+@TableName+''
--exec(@Sql)


     


END




GO