set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[ReleaseCursor] AS BEGIN SET NOCOUNT ON; DECLARE @cnt INT; SELECT @cnt=0; DECLARE @tbl_cursorname TABLE(id INT IDENTITY(1,1) NOT NULL,curname varchar(128),openstatus INT)
INSERT @tbl_cursorname(curname,openstatus) --取到所有的游标名字和是否打开,打开 1,关闭 0 SELECT a.cursor_name,a.open_status FROM sys.syscursors a DECLARE @rowcnt INT,@i INT; SELECT @rowcnt=count(*) FROM @tbl_cursorname; SELECT @i=1; DECLARE @cursorname VARCHAR(128),@openstatus INT; WHILE @i<=@rowcnt BEGIN SELECT @cursorname=curname,@openstatus=openstatus FROM @tbl_cursorname WHERE id=@i; SELECT @cursorname,@openstatus BEGIN TRY IF (@openstatus=1) --打开则关闭 EXEC('CLOSE '+@cursorname); EXEC('DEALLOCATE '+@cursorname); SET @cnt=@cnt+1; END TRY BEGIN CATCH END CATCH SET @i=@i+1; END END -- 在存储过程最后调用 exec ReleaseCursor
DEALLOCATE cursor_name ---释放游标
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ReleaseCursor]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cnt INT;
SELECT @cnt=0;
DECLARE @tbl_cursorname TABLE(id INT IDENTITY(1,1) NOT NULL,curname varchar(128),openstatus INT)
INSERT @tbl_cursorname(curname,openstatus) --取到所有的游标名字和是否打开,打开 1,关闭 0
SELECT a.cursor_name,a.open_status
FROM sys.syscursors a DECLARE @rowcnt INT,@i INT;
SELECT @rowcnt=count(*) FROM @tbl_cursorname;
SELECT @i=1;
DECLARE @cursorname VARCHAR(128),@openstatus INT; WHILE @i<=@rowcnt
BEGIN
SELECT @cursorname=curname,@openstatus=openstatus
FROM @tbl_cursorname
WHERE id=@i;
SELECT @cursorname,@openstatus
BEGIN TRY
IF (@openstatus=1) --打开则关闭
EXEC('CLOSE '+@cursorname);
EXEC('DEALLOCATE '+@cursorname);
SET @cnt=@cnt+1;
END TRY
BEGIN CATCH
END CATCH
SET @i=@i+1;
END
END
-- 在存储过程最后调用 exec ReleaseCursor