SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DeleteFromTable
@TableNameFrom nvarchar(100),
@TableNameTo nvarchar(100),
@LeaveCount int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TempTypeid int
DECLARE @SQLText nvarchar(3000)
SET @SQLText= 'SELECT DISTINCT typeid FROM '+ @TableNameFrom
DECLARE Cur_Typeid CURSOR Scroll
FOR
sp_executesql @SQLText
OPEN Cur_Typeid
FETCH first FROM Cur_Typeid INTO @TempTypeid
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @id int
DECLARE @typeid int
DECLARE @publishDate datetime
DECLARE @SqlQuery nvarchar(3000)
SET @SqlQuery= 'SELECT id, typeid, publishDate FROM ' [email protected]+ 'WHERE typeid= '[email protected]+ ' AND id NOT IN(SELECT TOP '[email protected]+ ' id FROM '[email protected]+ ' WHERE typeid= '[email protected]+ ') ORDER BY publishDate DESC '
DECLARE Cur_TableFrom CURSOR Scroll
FOR
exec sp_executesql @SqlQuery
OPEN Cur_TableFrom
FETCH first FROM Cur_TableFrom INTO @id, @typeid, @publishDate
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @SqlInsert nvarchar(2000)
SET @SqlInsert= 'INSERT INTO '+ @TableNameTo+ '(id, typeid, publishDate) VALUES ( '[email protected]+ ', '+ @typeid+ ', '+ @publishDate+ ') '
DECLARE @SqlDelete nvarchar(2000)
SET @SqlDelete= 'DELETE FROM '[email protected]+ ' WHERE id= '[email protected]
exec sp_executesql @SqlInsert
exec sp_executesql @SqlDelete
FETCH next FROM Cur_TableFrom INTO @id, @typeid, @publishDate
END
CLOSE Cur_TableFrom
DEALLOCATE Cur_TableFrom
FETCH next FROM Cur_Typeid INTO @TempTypeid
END
CLOSE Cur_Typeid
DEALLOCATE Cur_Typeid
END
GO
报错:
消息 102，级别 15，状态 1，过程 DeleteFromTable，第 25 行
'sp_executesql ' 附近有语法错误。
消息 156，级别 15，状态 1，过程 DeleteFromTable，第 39 行
关键字 'exec ' 附近有语法错误。
------解决方案--------------------
/*
将游标定以在字串里，例如：
*/
declare @name sysname
-- 将游标 Cursor_Test 定义在字串里：
exec sp_executesql N 'declare Cursor_Test cursor for select top 10 name from sysobjects '
open Cursor_Test
fetch next from Cursor_Test into @name
while @@fetch_status = 0
begin
print @name
fetch next from Cursor_Test into @name
end
close Cursor_Test
deallocate Cursor_Test
------解决方案--------------------
/*
第一处错误的地方可以这样改，第二处楼主参照修改，谢谢：
*/
SET @SQLText= 'DECLARE Cur_Typeid CURSOR Scroll for SELECT DISTINCT typeid FROM '+ @TableNameFrom