第一次写这么长的存储过程，存下来记录下

最新推荐文章于 2024-04-25 15:12:22 发布
最新推荐文章于 2024-04-25 15:12:22 发布
阅读量431 收藏
点赞数
文章标签： 存储 date sql insert output object
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/smahorse/article/details/7759574
版权 
USE [NIR]
GO
/****** Object:  StoredProcedure [dbo].[DBTools_NIR_MoveOldData]    Script Date: 2012/8/1 8:54:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------
-- 用途： 
-- 项目名称： 
-- 说明：
-- 时间： 
-- 编写者: 
--------------------------------------
-- 修改记录:
-- 编号	修改时间		修改人		修改原因		修改标注
------------------------------------*/
/*	测试语句
DECLARE @Return INT
EXEC DBTools_NIR_MoveOldData @StartTime='2000-01-01',@EndTime='2000-01-31',@Return=@Return OUTPUT
SELECT @Return
*/
ALTER PROCEDURE [dbo].[DBTools_NIR_MoveOldData]
(
	@StartTime DATETIME,
	@EndTime DATETIME,
	@Return INT OUTPUT,--@FunctionType=1时 @Return=1代表没有分库数据，@Return=2代表存在分库数据@FunctionType=2时 @Return=1代表分库成功，@Return=-1代表分库失败
	@FunctionType INT=1 --1表示测试是否有分库数据，2表示执行分库

)
AS
	BEGIN
		SET NOCOUNT ON 
		DECLARE @SQL NVARCHAR(MAX)
		SET @Return=1

		--建表记录需要处理的表名称,表记录数等信息
		CREATE TABLE #TEMP
		(
			TableName  NVARCHAR(50),
			RecCount INT,
			PreFix NVARCHAR(30),
			Suffix NVARCHAR(10)
		)

		CREATE TABLE #TEMP4 (DatabaseName NVARCHAR(20))
		--建立表记录日志库转存前目标表的记录数
		CREATE TABLE #TEMP5
		(
			DatabaseName NVARCHAR(20),
			TableName NVARCHAR(100),
			RecCount INT
		)
		INSERT INTO #TEMP4
			   SELECT name 
						FROM SYS.databases 
						WHERE name LIKE 'NIRLOG______'
						AND RIGHT(name,6)>=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)
						AND RIGHT(name,6)<=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)
		
		DECLARE @DatabaseName NVARCHAR(20)
		DECLARE TEMP4_CURSOR CURSOR
				FOR SELECT DatabaseName 
							FROM #TEMP4  
		OPEN  TEMP4_CURSOR 
		FETCH NEXT FROM TEMP4_CURSOR INTO @DatabaseName
		
		WHILE @@FETCH_STATUS=0
		BEGIN
			SET @SQL=N'
						USE '+@DatabaseName+'    
						INSERT INTO #TEMP5
						SELECT  '''+@DatabaseName+''' AS DatabaseName,
								T1.name AS TableName,
								T2.rows AS RecCount
									FROM SYS.TABLES T1
									INNER JOIN (SELECT OBJECT_NAME(id) AS name,rows ,indid 
																	FROM sysindexes 
																	WHERE indid<2) T2
									ON T1.name=T2.name
									WHERE REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX(''2'',T1.name)+1))>='''+CONVERT(NVARCHAR(8),@StartTime,112)+'''
										AND REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX(''2'',T1.name)+1))<='''+CONVERT(NVARCHAR(8),@EndTime,112)+'''
										AND (T1.name like ''NIR_log_ChatFetion________''
										OR T1.name like ''NIR_log_BBS________''
										OR T1.name like ''NIR_log_ChatFetionGroup________''
										OR T1.name like ''NIR_log_ChatMSN________''
										OR T1.name like ''NIR_log_ChatMSNGroup________''
										OR T1.name like ''NIR_log_ChatQQ________''
										OR T1.name like ''NIR_log_ChatQQGroup________''
										OR T1.name like ''NIR_log_ChatUC________''
										OR T1.name like ''NIR_log_ChatUCGroup________''
										OR T1.name like ''NIR_log_ChatYahoo________''
										OR T1.name like ''NIR_log_ChatYahooGroup________''
										OR T1.name like ''NIR_log_SearchKeyword________''
										OR T1.name like ''NIR_log_CyberLogin________''
										OR T1.name like ''NIR_re_ClientCyber________'')

						'
			EXEC(@SQL)			    
			FETCH NEXT FROM TEMP4_CURSOR INTO @DatabaseName
		END 
		CLOSE TEMP4_CURSOR
		DEALLOCATE TEMP4_CURSOR


		--查询需要处理的表对应的记录数、日期等信息
		INSERT INTO #TEMP
		SELECT  T1.name,
				T2.rows,
				SUBSTRING(T1.name,1,CHARINDEX('2',T1.name)-1),
				REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX('2',T1.name)+1))

					FROM SYS.TABLES T1
					INNER JOIN (SELECT OBJECT_NAME(id) AS name,rows  
													FROM sysindexes 
													WHERE indid<2) T2
					ON T1.name=T2.name
					WHERE REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX('2',T1.name)+1))>=CONVERT(NVARCHAR(8),@StartTime,112)
					 AND REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX('2',T1.name)+1))<=CONVERT(NVARCHAR(8),@EndTime,112)
					 AND (T1.name like 'NIR_log_ChatFetion________'
					   OR T1.name like 'NIR_log_BBS________'
					   OR T1.name like 'NIR_log_ChatFetionGroup________'
					   OR T1.name like 'NIR_log_ChatMSN________'
					   OR T1.name like 'NIR_log_ChatMSNGroup________'
					   OR T1.name like 'NIR_log_ChatQQ________'
					   OR T1.name like 'NIR_log_ChatQQGroup________'
					   OR T1.name like 'NIR_log_ChatUC________'
					   OR T1.name like 'NIR_log_ChatUCGroup________'
					   OR T1.name like 'NIR_log_ChatYahoo________'
					   OR T1.name like 'NIR_log_ChatYahooGroup________')
					 order by name
		
		--更新目标表的记录数，加上原来分库中的表的已存在的记录数，便于后面跟转存后的数据想比较
		UPDATE T1
		SET T1.RecCount=T1.RecCount+T2.RecCount
		FROM #TEMP T1
		INNER JOIN #TEMP5 T2
		ON T1.TableName = T2.TableName      

		IF EXISTS (SELECT 1
							FROM #TEMP)
		BEGIN  
			SET @Return=2
			IF @FunctionType=2
			BEGIN    
				--将目标表中的数据逐表复制到分库中
				DECLARE TEMP1_CURSOR CURSOR FOR 
						SELECT TableName,PreFix,Suffix
									FROM #TEMP

				DECLARE @TableName NVARCHAR(50),
						@PreFix NVARCHAR(30),
						@Suffix NVARCHAR(10),
						@ResultCount INT       --转存后表的记录数

				OPEN TEMP1_CURSOR
				FETCH NEXT FROM TEMP1_CURSOR INTO @TableName,@PreFix,@Suffix

				WHILE @@FETCH_STATUS=0
				BEGIN
					IF @PreFix='NIR_log_BBS'
					BEGIN
						SET @SQL=N'
						EXEC [dbo].[DataProxy_NIR_log_BBS_CreateTable] '+@TableName+','''+@Suffix+'''
						INSERT INTO NIRLOG'+LEFT(@Suffix,6)+'.dbo.'+@TableName+'
									(
										--Recno            , 
										UnitID           , 
										ClientNo         , 
										--ClientID         , 
										ComputerIP       , 
										ComputerMAC      , 
										HyperID          , 
										ClientName       , 
										NameSpell        , 
										Certificate_Type , 
										Certificate_Code , 
										CardType         , 
										NCardNo          , 
										OccurTime        , 
										Service_Type     , 
										Title            , 
										SubTitle         , 
										Content          , 
										TagName          , 
										Account          , 
										Password         , 
										URL              , 
										BBSFlag          , 
										ProtocolType       

									)	
						SELECT [UnitID]
							  ,[ClientNo]
							  ,[ComputerIP]
							  ,[ComputerMAC]
							  ,[HyperID]
							  ,[ClientName]
							  ,[NameSpell]
							  ,[Certificate_Type]
							  ,[Certificate_Code]
							  ,[CardType]
							  ,[NCardNo]
							  ,[OccurTime]
							  ,[Service_Type]
							  ,[Title]
							  ,[SubTitle]
							  ,[Content]
							  ,[TagName]
							  ,[Account]
							  ,[Password]
							  ,[URL]
							  ,[BBSFlag]
							  ,[ProtocolType]
							   FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
							   ORDER BY RecNo
						'
					END
					ELSE 	IF @PreFix='NIR_log_ChatFetion' 
							OR @PreFix='NIR_log_ChatMSN' 
							OR @PreFix='NIR_log_ChatQQ'
							OR @PreFix='NIR_log_ChatUC' 
							OR @PreFix='NIR_log_ChatYahoo'
					BEGIN
						SET @SQL=N'
						EXEC [dbo].[DataProxy_'+@PreFix+'_CreateTable] '+@TableName+','''+@Suffix+'''
						INSERT INTO NIRLOG'+LEFT(@Suffix,6)+'.dbo.'+@TableName+'
									(
										SessionID	,
										CyberID	 ,
										FriendCyberID	,
										OccurTime	,
										Way	        ,
										Content
									)	
						SELECT  SessionID	,
								CyberID	 ,       
								FriendCyberID	,
								OccurTime	,
								Way	        ,
								Content          
											 FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
											 ORDER BY RecNo
						'
					END  
					ELSE 	IF @PreFix='NIR_log_ChatFetionGroup' 
							OR @PreFix='NIR_log_ChatMSNGroup'
							OR @PreFix='NIR_log_ChatQQGroup' 
							OR @PreFix='NIR_log_ChatUCGroup' 
							OR @PreFix='NIR_log_ChatYahooGroup'

					BEGIN
						SET @SQL=N'
						EXEC [dbo].[DataProxy_'+@PreFix+'_CreateTable] '+@TableName+','''+@Suffix+'''
						INSERT INTO NIRLOG'+LEFT(@Suffix,6)+'.dbo.'+@TableName+'
									(
										SessionID   , 
										CyberID     , 
										ChatCyberID , 
										GroupID     , 
										OccurTime   , 
										Content           
									)	
						SELECT  SessionID   , 
								CyberID     , 
								ChatCyberID , 
								GroupID     , 
								OccurTime   , 
								Content       
											FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
											ORDER BY RecNo
						'
					END  

					EXEC(@SQL)
					--统计转存后日志表的记录数，如果和源数据表记录数相等，则删除原数据库表
					SET @SQL=N'USE NIRLOG'+LEFT(@Suffix,6)+'
						DECLARE @ResultCount INT       --转存后表的记录数
						 SELECT @ResultCount=rows 
											FROM sysindexes    
											WHERE indid<2
											AND  OBJECT_NAME(id)='''+@TableName+'''  
						IF @ResultCount= (SELECT RecCount 
														FROM #TEMP 
														WHERE TableName='''+@TableName+''')
						--PRINT ''1''
						DROP TABLE NIR.dbo.'+@TableName+'
						ELSE
							BEGIN 
								PRINT ''表NIR.dbo.'+@TableName+'数据转存出错，请操作的同事手工转存''
								SET @Return =-1
							END
						'
					--PRINT @SQL
					EXEC sp_executesql @SQL,N'@Return int OUTPUT',@Return OUTPUT
				FETCH NEXT FROM TEMP1_CURSOR INTO @TableName,@PreFix,@Suffix
				END
				CLOSE TEMP1_CURSOR
				DEALLOCATE TEMP1_CURSOR
			END
		END   

		--处理表NIR_log_SearchKeyword数据
		--建表记录需要处理的表名称,表记录数等信息
		CREATE TABLE #TEMP2
		(
			TableName  NVARCHAR(50),
			RecCount INT,
			PreFix NVARCHAR(30),
			Date NVARCHAR(10),
			Suffix VARCHAR(1)
		)

		--查询需要处理的表对应的记录数、日期等信息
		INSERT INTO #TEMP2(TableName,RecCount,PreFix,Date,Suffix)
		SELECT  T1.name AS TableName,
				T2.rows AS RecCount,
				SUBSTRING(T1.name,1,CHARINDEX('2',T1.name)-1) AS PreFix,
				LEFT(RIGHT(t1.name,7),6) AS Date,
				RIGHT(t1.name,1) AS Suffix	
					FROM SYS.TABLES T1
					INNER JOIN (SELECT OBJECT_NAME(id) AS name,rows  
													FROM sysindexes 
													WHERE indid<2) T2
					ON T1.name=T2.name
					WHERE T1.name like 'NIR_log_SearchKeyword_______'
					AND RIGHT(t1.name,7)>=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
												(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
													  ELSE NULL
													  END)
					AND RIGHT(t1.name,7)<=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
												(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
													  ELSE NULL
													  END)
					ORDER by T1.name

		
		IF EXISTS(SELECT 1 FROM #TEMP2)
		BEGIN
			SET @Return=2
			IF @FUNCTIONTYPE=2
			BEGIN				
				DECLARE @i INT,
						@Date2 NVARCHAR(8),
						@Sum INT,
						@Start INT,
						@End INT
				DECLARE TEMP2_CURSOR CURSOR 
						FOR SELECT  TableName,
									RecCount,
									PreFix,
									Date,
									Suffix
											FROM #TEMP2
				DECLARE	@RecCount INT,
						@Date NVARCHAR(10)
				OPEN TEMP2_CURSOR
				FETCH NEXT FROM TEMP2_CURSOR 
						   INTO @TableName,@RecCount,@PreFix,@Date,@Suffix
				WHILE @@FETCH_STATUS=0
				BEGIN
					IF RIGHT(@TableName,7)<>LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					AND RIGHT(@TableName,7)<>LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					BEGIN
						IF @Suffix='A'
						BEGIN
							SET @i=1
							SET @Sum=0
							WHILE @i<11
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
								EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount
												FROM #TEMP5 
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									SET @Sum=@Sum+@Count-@OldCount
									'
									EXEC sp_executesql @SQL,N'@Sum INT OUTPUT',@Sum OUTPUT
								SET @i=@i+1				
							END
							SET @SQL='IF '+CONVERT(NVARCHAR(8),@Sum)+'=(SELECT RecCount
																					FROM #TEMP2
																					WHERE TableName=''NIR_log_SearchKeyword'+@Date+'A'')
										DROP TABLE NIR.dbo.NIR_log_SearchKeyword'+@Date+'A
										ELSE 
										BEGIN 
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'A数据转存出错，请操作的同事手工转存''
											SET @Return =-1
										END
										'
							EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
						END
						ELSE IF @Suffix='B'
						BEGIN
							SET @i=11 
							SET @Sum=0  
							WHILE @i<21
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount
												FROM #TEMP5 
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									SET @Sum=@Sum+@Count-@OldCount
									'
									EXEC sp_executesql @SQL,N'@Sum INT OUTPUT',@Sum OUTPUT
								SET @i=@i+1				
							END
							SET @SQL='IF '+CONVERT(NVARCHAR(8),@Sum)+'=(SELECT RecCount
														FROM #TEMP2
														WHERE TableName=''NIR_log_SearchKeyword'+@Date+'B'')
										DROP TABLE NIR.dbo.NIR_log_SearchKeyword'+@Date+'B
										ELSE 
										BEGIN 
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'B数据转存出错，请操作的同事手工转存''
											SET @Return =-1
										END
										'
							EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
						END 
						ELSE IF @Suffix='C'
						BEGIN
							SET @i=21 
							SET @Sum=0  
							WHILE @i<DAY(DATEADD(DD,-1,DATEADD(mm,1,@Date+'01')))+1
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount
												FROM #TEMP5 
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									SET @Sum=@Sum+@Count-@OldCount
									'
									EXEC sp_executesql @SQL,N'@Sum INT OUTPUT',@Sum OUTPUT
								SET @i=@i+1				
							END
							SET @SQL='IF '+CONVERT(NVARCHAR(8),@Sum)+'=(SELECT RecCount
														FROM #TEMP2
														WHERE TableName=''NIR_log_SearchKeyword'+@Date+'C'')
										DROP TABLE NIR.dbo.NIR_log_SearchKeyword'+@Date+'C
										ELSE 
										BEGIN 
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'C数据转存出错，请操作的同事手工转存''
											SET @Return =-1
										END
										'
							EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
						END  
					END
					ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					OR RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					BEGIN
						IF @Suffix='A'
						BEGIN                  
							 IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							AND RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1
							 END 
							 ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=11
							 END 
							 ELSE IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=1
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1					 
							 END         
							SET @i=@Start
							SET @Sum=0
							WHILE @i<@End
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
								EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount 
												FROM #TEMP5
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									IF (@Count-@OldCount)=(SELECT COUNT(1)
																	FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A WITH(NOLOCK) 
																	WHERE OccurTime='''+@Date2+'''
																	AND  OccurTime<DATEADD(DD,1,'''+@Date2+'''))
									DELETE FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A
											WHERE OccurTime='''+@Date2+'''
											AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									ELSE 
									BEGIN
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'A数据转存出错，请操作的同事手工转存''
											SET @Return =-1
									END

									'
									EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
								SET @i=@i+1				
							END
						END
						ELSE IF @Suffix='B'
						BEGIN
							 IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							AND RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1
							 END 
							 ELSE IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=21
							 END 
							 ELSE IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=11
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1					 
							 END         
							SET @i=@Start
							SET @Sum=0
							WHILE @i<@End
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount 
												FROM #TEMP5
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									IF (@Count-@OldCount)=(SELECT COUNT(1)
																	FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B WITH(NOLOCK) 
																	WHERE OccurTime='''+@Date2+'''
																	AND  OccurTime<DATEADD(DD,1,'''+@Date2+'''))
									DELETE FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B
											WHERE OccurTime='''+@Date2+'''
											AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									ELSE 
									BEGIN
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'B数据转存出错，请操作的同事手工转存''
											SET @Return =-1
									END

									'
									EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
								SET @i=@i+1				
							END
						END 
						ELSE IF @Suffix='C'
						BEGIN
							 IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							AND RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1
							 END 
							 ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=DAY(DATEADD(DD,-1,DATEADD(mm,1,@Date+'01')))+1
							 END 
							 ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=21
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1					 
							 END        
							SET @i=@Start
							SET @Sum=0
							WHILE @i<@End                  
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount 
												FROM #TEMP5
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									IF (@Count-@OldCount)=(SELECT COUNT(1)
																	FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C WITH(NOLOCK) 
																	WHERE OccurTime='''+@Date2+'''
																	AND  OccurTime<DATEADD(DD,1,'''+@Date2+'''))
									DELETE FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C
											WHERE OccurTime='''+@Date2+'''
											AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									ELSE 
									BEGIN
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'C数据转存出错，请操作的同事手工转存''
											SET @Return =-1
									END

									'
									EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
								SET @i=@i+1				
							END
						END 
					END
					FETCH NEXT FROM TEMP2_CURSOR 
							INTO @TableName,@RecCount,@PreFix,@Date,@Suffix
				END              
				CLOSE TEMP2_CURSOR
				DEALLOCATE TEMP2_CURSOR
			END
		END

		--处理表NIR_log_TelSession\NIR_log_CyberLogin\NIR_re_ClientCyber的数据
		--建表记录需要处理的记录的日期及记录数
		CREATE TABLE #TEMP3
		(
			TableName NVARCHAR(100),
			Date NVARCHAR(8),
			Count INT
		)

		INSERT INTO #TEMP3
		SELECT  'NIR_log_CyberLogin' AS TableName,
				CONVERT(NVARCHAR(8),LoginTime,112) AS Date,
				count(distinct cast(SessionID as nvarchar(20))+convert(nvarchar(20),LoginTime,120)) AS Count
						FROM NIR_log_CyberLogin WITH(NOLOCK) 
						WHERE LoginTime>=@StartTime
						AND LoginTime<DATEADD(DD,1,@EndTime)
						GROUP BY CONVERT(NVARCHAR(8),LoginTime,112)
		UNION ALL
		SELECT  'NIR_log_TelSession' AS TableName,
				CONVERT(NVARCHAR(8),OccurTime,112) AS Date,
				COUNT(1) AS Count
						FROM NIR_log_TelSession WITH(NOLOCK) 
						WHERE OccurTime>=@StartTime
						AND OccurTime<DATEADD(DD,1,@EndTime)
						GROUP BY CONVERT(NVARCHAR(8),OccurTime,112)
		UNION ALL 
		SELECT  'NIR_re_ClientCyber' AS TableName,
				CONVERT(NVARCHAR(8),RecordTime,112) AS Date,
				COUNT(1) AS Count
						FROM NIR_re_ClientCyber WITH(NOLOCK) 
						WHERE RecordTime>=@StartTime
						AND RecordTime<DATEADD(DD,1,@EndTime)
						GROUP BY CONVERT(NVARCHAR(8),RecordTime,112)
				ORDER BY TableName,Date

		IF EXISTS(SELECT 1
								FROM #TEMP3)
		BEGIN
			SET @Return=2
			IF @FUNCTIONTYPE=2
			BEGIN      
				DECLARE TEMP3_CURSOR CURSOR FOR 
						SELECT TableName,Date FROM #TEMP3	
				OPEN TEMP3_CURSOR
				FETCH NEXT FROM TEMP3_CURSOR INTO  @TableName,@Date
				WHILE @@FETCH_STATUS=0
				BEGIN
					IF @TableName='NIR_log_CyberLogin'
					BEGIN
						SET @SQL=N'
							EXEC NIR.dbo.DataProxy_'+@TableName+'_CreateTable ''1'','''+@Date+'''
							INSERT INTO NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+@Date+'
										(
											SessionID	,
											LoginTime	,
											--Recno		,
											CyberID		,
											UnitID		,
											LogoutTime	 
										)
							SELECT  SessionID,
									LoginTime,
									MAX(CyberID) AS CyberID,
									MAX(UnitID) AS UnitID,
									MAX(LogoutTime) AS LogoutTime
									FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
									WHERE LoginTime>='''+@Date+'''
									AND LoginTime<DATEADD(DD,1,'''+@Date+''')
									GROUP BY SessionID,LoginTime
							'
						EXEC (@SQL)
						SET @SQL=N'USE NIRLOG'+LEFT(@Date,6)+'
							DECLARE @Count INT,@OldCount INT
							SET @Count=0
							SET @OldCount=0
							IF EXISTS(SELECT 1 
												FROM #TEMP5
												WHERE TableName='''+@TableName+@Date+''')
							SELECT @OldCount=RecCount
										FROM #TEMP5
										WHERE TableName='''+@TableName+@Date+'''
							SELECT @Count=rows FROM sysindexes
												WHERE indid<2
												AND OBJECT_NAME(id)='''+@TableName+@Date+'''
							IF (@Count-@OldCount)=(SELECT count(1) FROM 
																	(SELECT DISTINCT SessionID,LoginTime 
																			FROM NIR.dbo.NIR_log_CyberLogin T2
																			where T2.LoginTime>='''+@Date+'''
																			and T2.LoginTime<DATEADD(DD,1,'''+@Date+''')
																	)B)
							DELETE FROM NIR.dbo.'+@TableName+'
										WHERE LoginTime>='''+@Date+'''
										AND LoginTime<DATEADD(DD,1,'''+@Date+''')
							ELSE 
							BEGIN
									PRINT ''表NIR.dbo.'+@TableName+' '+@Date+'这天的数据转存出错，请操作的同事手工转存''
									SET @Return =-1
							END              
							'
						EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
					END
					ELSE IF @TableName='NIR_log_TelSession'
					BEGIN
						SET @SQL=N'
							EXEC NIR.dbo.DataProxy_'+@TableName+'_CreateTable ''1'','''+@Date+'''
							--先记录转存数据前目标表的指定日期内原有数据量
							INSERT INTO #TEMP5
							SELECT ''NIRLOG'+LEFT(@Date,6)+''' AS DatabaseName,
								   '''+@TableName+@Date+''' AS TableName,
								   COUNT(1) AS RecCount
										FROM NIRLOG'+LEFT(@Date,6)+'.dbo.NIR_log_TelSession
										WHERE OccurTime>='''+@Date+'''
										AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							INSERT INTO NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+'
										(
											--RecNo		,
											TelId		,
											SessionID	,
											FriendCyberID	,
											OccurTime	 	 
										)
							SELECT  --RecNo		,
									TelId		,
									SessionID	,
									FriendCyberID	,
									OccurTime	 
									FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
									WHERE OccurTime>='''+@Date+'''
									AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							'
						EXEC (@SQL)
						SET @SQL=N'USE NIRLOG'+LEFT(@Date,6)+'
							DECLARE @Count INT,@OldCount INT
							SET @Count=0
							SET @OldCount=0
							IF EXISTS(SELECT 1 
												FROM #TEMP5
												WHERE TableName='''+@TableName+@Date+''')
							SELECT @OldCount=RecCount
										FROM #TEMP5
										WHERE TableName='''+@TableName+@Date+'''
							SELECT @Count=COUNT(1) FROM NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+' WITH(NOLOCK) 
												   WHERE OccurTime>='''+@Date+'''
												   AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							IF (@Count-@OldCount)=(SELECT Count 
															FROM #TEMP3
															WHERE Date='''+@Date+'''
															AND TableName='''+@TableName+''')
							DELETE FROM NIR.dbo.'+@TableName+'
										WHERE OccurTime>='''+@Date+'''
										AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							ELSE 
							BEGIN
									PRINT ''表NIR.dbo.'+@TableName+' '+@Date+'这天的数据转存出错，请操作的同事手工转存''
									SET @Return =-1
							END              
							'
						EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
					END
					ELSE IF @TableName='NIR_re_ClientCyber'
					BEGIN
						SET @SQL=N'
							EXEC NIR.dbo.DataProxy_'+@TableName+'_CreateTable ''1'','''+@Date+'''
							INSERT INTO NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+@Date+'
										(
											UnitID		,
											ClientNo	,
											CyberID		,
											CyberType	,
											RecordTime	,
											--ClientID	,
											SessionID	 
 
										)
							SELECT  UnitID		,
									ClientNo	,
									CyberID		,
									CyberType	,
									RecordTime	,
									--ClientID	,
									SessionID	 
									FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
									WHERE RecordTime>='''+@Date+'''
									AND RecordTime<DATEADD(DD,1,'''+@Date+''')
							'
						EXEC (@SQL)
						SET @SQL=N'USE NIRLOG'+LEFT(@Date,6)+'
							DECLARE @Count INT,@OldCount INT
							SET @Count=0
							SET @OldCount=0
							IF EXISTS(SELECT 1 
												FROM #TEMP5
												WHERE TableName='''+@TableName+@Date+''')
							SELECT @OldCount=RecCount
										FROM #TEMP5
										WHERE TableName='''+@TableName+@Date+'''
							SELECT @Count=rows FROM sysindexes
												WHERE indid<2
												AND OBJECT_NAME(id)='''+@TableName+@Date+'''
							IF (@Count-@OldCount)=(SELECT Count 
																FROM #TEMP3
																WHERE Date='''+@Date+'''
																AND TableName='''+@TableName+''')
							DELETE FROM NIR.dbo.'+@TableName+'
										WHERE RecordTime>='''+@Date+'''
										AND RecordTime<DATEADD(DD,1,'''+@Date+''')
							ELSE 
							BEGIN
									PRINT ''表NIR.dbo.'+@TableName+' '+@Date+'这天的数据转存出错，请操作的同事手工转存''
									SET @Return =-1
							END              
							'
						EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
					END   
					FETCH NEXT FROM TEMP3_CURSOR INTO  @TableName,@Date       		
				END 
				CLOSE TEMP3_CURSOR
				DEALLOCATE TEMP3_CURSOR   
			END
		END  
	END








                

        

        

    




    

      

        

            

              
                
                  smahorse
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      










                



	

		

			

				
					
Linux 下mysql通过存储过程实现批量生成记录

				
			

			

				

					09-09
				

			

		

		

			
				
主要介绍了Linux 下mysql通过存储过程实现批量生成记录的相关资料,需要的朋友可以参考下

			
		

	



                

              
                



	

		

			

				
					
最长的存储过程

				
			

			

				

					钢铁加鲁鲁二号的专栏
				

				

					04-21
					
					1064
					
				

			

		

		

			
				
--好吧，我承认这是我学SQL两年以来写过的最长的SQL存储过程了，没有别的意思，只是发布上来纪念一下~alter  PROCEDURE [dbo].[Sp_投资公司统一收据] (
   @StaffID numeric (18, 0), @SqlWhere nvarchar (500)
)
  -- WITH EXECUTE AS CALLER
AS
IF NOT EXISTS
(SELECT

			
		

	



                


  
              

                


	

		

			

				
					
存储过程--把执行的结果 赋值给变量

				
			

			

				

					weixin_30757793的博客
				

				

					05-06
					
					510
					
				

			

		

		

			
				
USE [InformationSystem]
GO
/****** Object:  StoredProcedure [dbo].[pro_oper]    Script Date: 05/06/2015 16:07:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================...

			
		

	





	

		

			

				
					
ORACLE存储过程

					
热门推荐

				
			

			

				

					胆小鬼的技术庄园
				

				

					11-02
					
					24万+
					
				

			

		

		

			
				
oracle存储过程

目录

    一.什么是存储过程

二.为什么要写存储过程

三.存储过程基础

1.存储过程结构

2.存储过程语法

3.pl/sql处理存储过程

四.存储过程进阶

1.BUIK COLLECT

2.FORALL

3.pl/sql调试存储过程

4.案例实战
...

			
		

	



		

			
		



	

		

			

				
					
SQL Server数据库基础知识——数据库存储过程怎么写

				
			

			

				

					weixin_47258773的博客
				

				

					05-22
					
					3万+
					
				

			

		

		

			
				
SQL Server数据库基础知识
存储过程概述

什么是存储过程？
存储过程的种类
如何创建、修改、删除、调用存储过程？
存储过程的优缺点
存储过程和触发器的区别?
存储过程和函数的区别?
存储过程的使用

1.    什么是存储过程？
存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个“execute 存储过程名 参数”命令来调用存储过程。
2.    存储过程的种类


			
		

	





	

		

			

				
					
Sql server存储过程以及一次插入多条记录

				
			

			

				

					瑛民星的专栏
				

				

					02-21
					
					7461
					
				

			

		

		

			
				
首先简单介绍sql server存储过程吧。至于概念含义啥的就不做过多介绍了。它其实和mysql有些类似，语法大同小异。
还是用例子说明问题吧。
CREATE PROCEDURE insert_supplier
@supplier_name varchar(30),
@supplier_contact varchar(30),
--------------------------------输

			
		

	





	

		

			

				
					
java编程调用存储过程中得到新增记录id号的实现方法

				
			

			

				

					09-03
				

			

		

		

			
				
主要介绍了java编程调用存储过程中得到新增记录id号的实现方法,涉及Java数据库操作中存储过程的相关使用技巧,需要的朋友可以参考下

			
		

	





	

		

			

				
					
mysql存储过程基础之遍历多表记录后插入第三方表中详解

				
			

			

				

					09-09
				

			

		

		

			
				
主要给大家介绍了关于mysql存储过程教程之遍历多表记录后插入第三方表中的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面来一起看看吧

			
		

	





	

		

			

				
					
MySQL实现创建存储过程并循环添加记录的方法

				
			

			

				

					09-09
				

			

		

		

			
				
主要介绍了MySQL实现创建存储过程并循环添加记录的方法,涉及基本的mysql存储过程创建、调用相关操作技巧,需要的朋友可以参考下

			
		

	





	

		

			

				
					
Python + SQLAlchemy操作MySQL数据库（ORM）

				
			

			

				

					上庸者-不服周
				

				

					04-19
					
					265
					
				

			

		

		

			
				
SQLAlchemy是用Python编程语言开发的一个开源项目。它提供了SQL工具包和ORM（对象关系映射）工具，使用MIT许可证发行。SQLAlchemy采用简单的Python语言，提供高效和高性能的数据库访问，实现了完整的企业级持久模型。它的理念是SQL数据库的量级和性能比对象集合重要，而对象集合的抽象又重要于表和行。

			
		

	





	

		

			

				
					
SQL表连接详解：JOIN与逗号（,）的使用及其性能影响

				
			

			

				

					好好学习日记
				

				

					04-17
					
					1061
					
				

			

		

		

			
				
在这个详细的解释中，我们将深入探讨SQL中表连接的概念，特别是JOIN和逗号（,）在连接表时的不同用法及其对查询性能的影响。通过实际示例和背后的逻辑分析，我们将揭示在不同场景下选择哪种连接方式更为合适。

			
		

	





	

		

			

				
					
探秘数据库中间件：ProxySQL与MaxScale的优势与劣势

				
			

			

				

					todoitbo的博客
				

				

					04-22
					
					1080
					
				

			

		

		

			
				
本文将深入探讨两个流行的数据库负载均衡中间件ProxySQL和MaxScale的功能、特点以及适用场景。我们将比较两者的优劣势，并提供选择和配置的建议，帮助读者了解如何在自己的数据库架构中选择合适的解决方案。

			
		

	





	

		

			

				
					
苍穹外卖day10（1）Spring Task、 订单状态定时处理

				
			

			

				

					qq_51076441的博客
				

				

					04-22
					
					454
					
				

			

		

		

			
				
Spring Task用于在指定时间处理某个业务逻辑，在本项目中体现在订单状态定时处理，包括“下单后未支付（订单超时自动取消）”、“订单一直处于派送中状态（订单需要自动更新成已完成）”。实现的业务逻辑通过当前订单状态和下单时间去查询订单，对于满足上面两个条件的订单，设置一个时间让任务自动执行。Spring框架提供的任务调度工具，可以按照约定的时间自动执行某个代码逻辑。应用场景信用卡每月还款提醒银行贷款每月还款提醒火车票售票系统处理未支付订单入职纪念日为用户发送通知。

			
		

	





	

		

			

				
					
Oracle导出导入dmp等文件类型的多表数据的常用方法、遇见的常见问题和解决办法（exp无效sql？？？）

					
最新发布

				
			

			

				

					路漫漫其修远兮，吾将上下而求索。小手点一波关注进阶的李仙桎，我们一起携手进步，
				

				

					04-25
					
					175
					
				

			

		

		

			
				
但是如果你的本机没有安装有Oracle数据库，使用的instant client远程连接服务器上的Oracle数据库时候，你没有Oracle数据库带有的exp.exe、imp.exe等扩展文件，所以你运行的时候多半也出现无效sql的错误，例如我用PLSQL导出表数据的时候就发生了类似的错误。

			
		

	





	

		

			

				
					
高频SQL 判断三角形

				
			

			

				

					weixin_52956719的博客
				

				

					04-22
					
					369
					
				

			

		

		

			
				
在 SQL 中，(x, y, z)是该表的主键列。该表的每一行包含三个线段的长度。对每三个线段报告它们是否可以形成一个三角形。以返回结果表。查询结果格式如下所示。

			
		

	





	

		

			

				
					
【SQL每日一练】分组过滤练习题

				
			

			

				

					LP'S Blog
				

				

					04-18
					
					458
					
				

			

		

		

			
				
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);

			
		

	





	

		

			

				
					
PostgreSQL中的索引类型有哪些，以及何时应选择不同类型的索引？

				
			

			

				

					墨松笔记
				

				

					04-19
					
					822
					
				

			

		

		

			
				
选择合适的索引类型对于提高PostgreSQL数据库的查询性能至关重要。根据列的数据类型、查询需求和数据分布情况，可以选择合适的索引类型。上述解决方案和示例代码展示了如何在PostgreSQL中创建不同类型的索引，并简要说明了它们的适用场景。在实际应用中，还需要根据具体情况进行调整和优化。相关阅读推荐。

			
		

	





	

		

			

				
					
在PostgreSQL中如何处理大对象（Large Objects），例如存储和检索二进制文件？

				
			

			

				

					墨松笔记
				

				

					04-19
					
					975
					
				

			

		

		

			
				
PostgreSQL 提供了对大对象（Large Objects）的支持，这是一种特殊的数据类型，用于存储大量数据，通常用于存储二进制文件或大型文本数据。大对象存储在数据库外部，但在数据库内部进行管理，从而允许您像处理普通数据库对象一样处理它们。您需要提供大对象的 OID、要写入的偏移量（从文件开始的位置）以及要写入的数据。以下是在 PostgreSQL 中处理大对象的基本步骤，包括存储和检索二进制文件的示例代码。这个函数返回一个 OID（对象标识符），您可以将其用作大对象的引用。函数从大对象中读取数据。

			
		

	





	

		

			

				
					
如果在sqlsever中写一个存储过程来向表里添加新的记录

				
			

			

				

					06-06
				

			

		

		

			
				
好的，以下是一个简单的存储过程，用于向指定的表中添加新的记录：

```
CREATE PROCEDURE AddNewRecord
    @FirstName varchar(50),
    @LastName varchar(50),
    @Email varchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO MyTable (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email)
END
```

其中，`MyTable`是您要添加记录的表名，`@FirstName`、`@LastName`和`@Email`是存储过程的输入参数，表示要添加的记录的相关信息。您可以根据实际情况修改参数名称、表名和列名。执行存储过程的语句如下：

```
EXEC AddNewRecord 'John', 'Doe', '[email protected]'
```

执行该语句后，将在`MyTable`表中添加一条新的记录，包含`FirstName`、`LastName`和`Email`三个字段，对应的值为`'John'`、`'Doe'`和`'[email protected]'`。

			
		

	



              



  
“相关推荐”对你有帮助么？

  

      
    
          
  • 
              
    
                  
                  
              
    
              
    非常没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    一般
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    有帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    非常有帮助
    
          
    • 
      

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值