SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ONDECLARE @TABLE1 TABLE(NAME VARCHAR(100)) DECLARE @TABLE2 TABLE (NIANYUE VARCHAR(100)) DECLARE @DISTANT INT DECLARE @SQL1 VARCHAR(100) DECLARE @SQL2 VARCHAR(100)SELECT @DISTANT=DS_DataStore_Int FROM Global_DatabaseSpace WHERE DS_Id_Int=1INSERT INTO @TABLE1 SELECT NAME FROM PTRSD..SysObjects WHERE XType='U' AND NAME LIKE 'S_AlarmInfo2%' ORDER BY NAMEINSERT INTO @TABLE2 SELECT NAME FROM PTRSD..SysObjects WHERE XType='U' AND NAME LIKE 'Global_PerMonth2%' ORDER BY NAMEDELETE FROM S_AlarmInfo_New WHERE AI_AlarmDate_Date<DATEADD(MONTH,-@DISTANT,GETDATE())IF EXISTS ( SELECT 1 FROM @TABLE1 WHERE CAST(SUBSTRING(NAME,12,8)AS DATETIME) < DATEADD(MONTH,-@DISTANT,GETDATE()) ) BEGIN DECLARE CURSOR1 CURSOR FOR SELECT NAME FROM @TABLE1 OPEN CURSOR1 DECLARE @NAME1 VARCHAR(100) FETCH NEXT FROM CURSOR1 INTO @NAME1WHILE @@FETCH_STATUS=0 BEGIN IF CAST(SUBSTRING(@NAME1,12,8)AS DATETIME) < DATEADD(MONTH,-@DISTANT,GETDATE()) BEGIN SET @SQL1='DROP TABLE '+ @NAME1 EXEC ( @SQL1 ) END FETCH NEXT FROM CURSOR1 INTO @NAME1 END CLOSE CURSOR1 DEALLOCATE CURSOR1 ENDIF EXISTS ( SELECT 1 FROM @TABLE2 WHERE (CONVERT(INT,SUBSTRING(NIANYUE,16,4)) < DATEPART(YYYY,DATEADD(MONTH,-@DISTANT,GETDATE())) OR( CONVERT(INT,SUBSTRING(NIANYUE,16,4)) = DATEPART(YYYY,DATEADD(MONTH,-@DISTANT,GETDATE())) AND CONVERT(INT,SUBSTRING(NIANYUE,20,2)) <= DATEPART(MM,DATEADD(MONTH,-@DISTANT,GETDATE())))) ) BEGIN DECLARE CURSOR2 CURSOR FOR SELECT NIANYUE FROM @TABLE2 OPEN CURSOR2 DECLARE @NAME2 VARCHAR(100) FETCH NEXT FROM CURSOR2 INTO @NAME2 WHILE @@FETCH_STATUS=0 BEGIN IF CONVERT(INT,SUBSTRING(@NAME2,16,4)) < DATEPART(YYYY,DATEADD(MONTH,-@DISTANT,GETDATE())) BEGIN SET @SQL2='DROP TABLE '+ @NAME2 EXEC ( @SQL2) END ELSE IF CONVERT(INT,SUBSTRING(@NAME2,20,2)) < DATEPART(MM,DATEADD(MONTH,-@DISTANT,GETDATE())) BEGIN SET @SQL2='DROP TABLE '+ @NAME2 EXEC ( @SQL2) END ELSE BEGIN SET @SQL2='DELETE FROM '+ @NAME2 +' WHERE DayBelong_Int'+' < '+ ' DATEPART(DD,DATEADD(MONTH,-@DISTANT,GETDATE()))' EXEC ( @SQL2 ) END FETCH NEXT FROM CURSOR2 INTO @NAME2 END CLOSE CURSOR2 DEALLOCATE CURSOR2 END
INSERT INTO @TABLE1 SELECT NAME FROM PTRSD..SysObjects WHERE XType='U' AND NAME LIKE 'S_AlarmInfo2%' ORDER BY NAMEINSERT INTO @TABLE2 SELECT NAME FROM PTRSD..SysObjects WHERE XType='U' AND NAME LIKE 'Global_PerMonth2%' ORDER BY NAME
直接SET @SQL2='DROP TABLE '+@NAME2
注意空格,试下
把所有字符加减中的以上字符的引号去掉
SET QUOTED_IDENTIFIER ONDECLARE @TABLE1 TABLE(NAME VARCHAR(100))
DECLARE @TABLE2 TABLE (NIANYUE VARCHAR(100))
DECLARE @DISTANT INT
DECLARE @SQL1 VARCHAR(100)
DECLARE @SQL2 VARCHAR(100)SELECT @DISTANT=DS_DataStore_Int
FROM Global_DatabaseSpace
WHERE DS_Id_Int=1INSERT INTO @TABLE1
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'S_AlarmInfo2%'
ORDER BY NAMEINSERT INTO @TABLE2
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'Global_PerMonth2%'
ORDER BY NAMEDELETE FROM S_AlarmInfo_New
WHERE AI_AlarmDate_Date<DATEADD(MONTH,-@DISTANT,GETDATE())IF EXISTS
(
SELECT 1 FROM @TABLE1 WHERE CAST(SUBSTRING(NAME,12,8)AS DATETIME) < DATEADD(MONTH,-@DISTANT,GETDATE())
)
BEGIN
DECLARE CURSOR1 CURSOR FOR SELECT NAME FROM @TABLE1
OPEN CURSOR1
DECLARE @NAME1 VARCHAR(100)
FETCH NEXT FROM CURSOR1 INTO @NAME1WHILE @@FETCH_STATUS=0
BEGIN
IF CAST(SUBSTRING(@NAME1,12,8)AS DATETIME) < DATEADD(MONTH,-@DISTANT,GETDATE())
BEGIN
SET @SQL1='DROP TABLE '+ @NAME1
EXEC ( @SQL1 )
END
FETCH NEXT FROM CURSOR1 INTO @NAME1
END
CLOSE CURSOR1
DEALLOCATE CURSOR1
ENDIF EXISTS
(
SELECT 1
FROM @TABLE2
WHERE (CONVERT(INT,SUBSTRING(NIANYUE,16,4)) < DATEPART(YYYY,DATEADD(MONTH,-@DISTANT,GETDATE()))
OR( CONVERT(INT,SUBSTRING(NIANYUE,16,4)) = DATEPART(YYYY,DATEADD(MONTH,-@DISTANT,GETDATE()))
AND CONVERT(INT,SUBSTRING(NIANYUE,20,2)) <= DATEPART(MM,DATEADD(MONTH,-@DISTANT,GETDATE()))))
)
BEGIN
DECLARE CURSOR2 CURSOR FOR SELECT NIANYUE FROM @TABLE2
OPEN CURSOR2
DECLARE @NAME2 VARCHAR(100)
FETCH NEXT FROM CURSOR2 INTO @NAME2
WHILE @@FETCH_STATUS=0
BEGIN
IF CONVERT(INT,SUBSTRING(@NAME2,16,4)) < DATEPART(YYYY,DATEADD(MONTH,-@DISTANT,GETDATE()))
BEGIN
SET @SQL2='DROP TABLE '+ @NAME2
EXEC ( @SQL2)
END
ELSE IF CONVERT(INT,SUBSTRING(@NAME2,20,2)) < DATEPART(MM,DATEADD(MONTH,-@DISTANT,GETDATE()))
BEGIN
SET @SQL2='DROP TABLE '+ @NAME2
EXEC ( @SQL2)
END
ELSE BEGIN
SET @SQL2='DELETE FROM '+ @NAME2 +' WHERE DayBelong_Int'+' < '+ ' DATEPART(DD,DATEADD(MONTH,-@DISTANT,GETDATE()))'
EXEC ( @SQL2 )
END
FETCH NEXT FROM CURSOR2 INTO @NAME2
END
CLOSE CURSOR2
DEALLOCATE CURSOR2
END
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'S_AlarmInfo2%'
ORDER BY NAMEINSERT INTO @TABLE2
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'Global_PerMonth2%'
ORDER BY NAME