cursordemo13.sql

T-SQL示例大全

/* 文件名称: CursorDemo13.sql */
USE 北风贸易
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.DealCursorProc') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.DealCursorProc
GO

SET NOCOUNT ON
GO

-- 建立一个拥有数据指针输出参数的存储过程
CREATE PROCEDURE DealCursorProc @OutCrsr CURSOR VARYING OUTPUT
AS
-- 建立一个与数据指针相关联的数据指针变量
SET @OutCrsr = CURSOR FOR
SELECT 姓名
FROM 飞狐工作室
WHERE 姓名 LIKE '王%'

-- 欲将数据指针以输出参数返回,务必先开启数据指针
OPEN @OutCrsr
GO

-- 声明一个局部的数据指针变量以便用来接收从存储过程返回来的数据指针
DECLARE @CrsrVar CURSOR

-- 执行先前建立的存储过程并接收传递回来的数据指针
EXEC DealCursorProc @OutCrsr = @CrsrVar OUTPUT

DECLARE @Name nvarchar(10)

FETCH NEXT FROM @CrsrVar INTO @Name

WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT @Name
   FETCH NEXT FROM @CrsrVar INTO @Name
END

CLOSE @CrsrVar

DEALLOCATE @CrsrVar
GO