MSSQL存储过程与游标的结合
【例3】结合游标和存储过程，打印所有学生名称
Sql代码
IF EXISTS(SELECT name FROM sysobjects where name='getStudents' and type='p')
DROP PROC getStudents
GO
CREATE PROC getStudents
@resultCusor CURSOR VARYING OUTPUT --声明游标
AS
SET @resultCusor = CURSOR --初始化游标
FOR
SELECT sno,sname FROM dbo.student
OPEN @resultCusor—打开游标
GO
--下边建立另外一个存储过程，用于遍历游标输出结果
IF EXISTS(SELECT name FROM sysobjects where name='printStudents' and type='p')
DROP PROC printStudents
GO
CREATE PROC printStudents
AS
DECLARE @sno varchar(20)
DECLARE @sname varchar(25)
DECLARE @result CURSOR—声明游标
EXEC getStudents @result OUTPUT –执行游标
FETCH NEXT FROM @result –推进游标
INTO @sno,@sname
While(@@Fetch_Status = 0) --循环游标
BEGIN
Begin
Print @sno + ':' + @sname
End
Fetch Next From @result --随着循环，推荐游标
InTo @sno,@sname
END
CLOSE @result
Deallocate @result
Go
--测试存储过程
EXEC printStudents
