SQL 请教这代码到底错哪了
SQL 请问这代码到底哪里错了
题目是：表S_AlarmInfo_New存储的是所有已发生的报警信息
Global_DatabaseSpace中DS_DataStore_Int字段表示保存几个月内的记录，我们取第一条
每发生一条报警会生成一张表S_AlarmInfo20141117，后八位表示报警发生日期
另外 有表Global_PerMonth201407存储7月份的报警其他信息。
现在，我们只保留系统规定的时间段内的表和记录，程序如下：
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @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=1
INSERT INTO @TABLE1
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'S_AlarmInfo2%'
ORDER BY NAME
INSERT INTO @TABLE2
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'Global_PerMonth2%'
ORDER BY NAME
DELETE 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 @NAME1
WHILE @@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
END
IF 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
总是会出现这样的错误提示：
这是怎么回事呢？？求大神debug
------解决思路----------------------
你那些SET @SQL2='DROP TABLE'+' @NAME2'
直接SET @SQL2='DROP TABLE '+@NAME2
注意空格，试下
------解决思路----------------------
' @NAME2'--改为 @NAME2
把所有字符加减中的以上字符的引号去掉
------解决思路----------------------
题目是：表S_AlarmInfo_New存储的是所有已发生的报警信息
Global_DatabaseSpace中DS_DataStore_Int字段表示保存几个月内的记录，我们取第一条
每发生一条报警会生成一张表S_AlarmInfo20141117，后八位表示报警发生日期
另外 有表Global_PerMonth201407存储7月份的报警其他信息。
现在，我们只保留系统规定的时间段内的表和记录，程序如下：
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @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=1
INSERT INTO @TABLE1
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'S_AlarmInfo2%'
ORDER BY NAME
INSERT INTO @TABLE2
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'Global_PerMonth2%'
ORDER BY NAME
DELETE 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 @NAME1
WHILE @@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
END
IF 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
总是会出现这样的错误提示：
这是怎么回事呢？？求大神debug
------解决思路----------------------
你那些SET @SQL2='DROP TABLE'+' @NAME2'
直接SET @SQL2='DROP TABLE '+@NAME2
注意空格，试下
------解决思路----------------------
' @NAME2'--改为 @NAME2
把所有字符加减中的以上字符的引号去掉
------解决思路----------------------
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @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=1
INSERT INTO @TABLE1
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'S_AlarmInfo2%'
ORDER BY NAME
INSERT INTO @TABLE2
SELECT NAME FROM PTRSD..SysObjects
WHERE XType='U' AND NAME LIKE 'Global_PerMonth2%'
ORDER BY NAME
DELETE 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 @NAME1
WHILE @@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
END
IF 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