例如create table #tmp( Name nvarchar(128), Owner nvarchar(128), Object_type nvarchar(31) ) insert into #tmp exec sp_help select * from #tmp drop table #tmp
CREATE PROCEDURE sp2 ASdeclare @a int exec @a=sp1 select @a
1.在SP2进行定义时,可以包含一个cursor类型参数,如下所示: create procedure sp2(@cursor_return cursor varying output,....) as ...... set @cursor_return=cursor for select语句... for read only ...... open @cursor_return ...... 2.在sp1进行调用时候,可以引用如下格式: create procedure sp2...... as ...... declare @macursor cursor exec sp1 @cursor_return = @macursor output ...... fetch @mycursor into ...... while (@@fetch_status =0) begin ...do other work fetch @mycursor into ...... end close @mycursor deallocate @mycursor ......
写错了一个字符,应该是这样: 1.在SP2进行定义时,可以包含一个cursor类型参数,如下所示: create procedure sp2(@cursor_return cursor varying output,....) as ...... set @cursor_return=cursor for select语句... for read only ...... open @cursor_return --进行数据的传递,而不是紧紧游标声明语句的传递,更严谨一些 ...... 2.在sp1进行调用时候,可以引用如下格式: create procedure sp2...... as ...... declare @mycursor cursor exec sp1 @cursor_return = @mycursor output ...... fetch @mycursor into ...... while (@@fetch_status =0) begin ...do other work fetch @mycursor into ...... end close @mycursor deallocate @mycursor ......
insert into #tmp exec sp_help
select * from #tmp
drop table #tmp
exec @a=sp1
select @a
create procedure sp2(@cursor_return cursor varying output,....)
as
......
set @cursor_return=cursor for select语句... for read only
......
open @cursor_return
......
2.在sp1进行调用时候,可以引用如下格式:
create procedure sp2......
as
......
declare @macursor cursor
exec sp1 @cursor_return = @macursor output
......
fetch @mycursor into ......
while (@@fetch_status =0)
begin
...do other work
fetch @mycursor into ......
end close @mycursor
deallocate @mycursor
......
1.在SP2进行定义时,可以包含一个cursor类型参数,如下所示:
create procedure sp2(@cursor_return cursor varying output,....)
as
......
set @cursor_return=cursor for select语句... for read only
......
open @cursor_return --进行数据的传递,而不是紧紧游标声明语句的传递,更严谨一些
......
2.在sp1进行调用时候,可以引用如下格式:
create procedure sp2......
as
......
declare @mycursor cursor
exec sp1 @cursor_return = @mycursor output
......
fetch @mycursor into ......
while (@@fetch_status =0)
begin
...do other work
fetch @mycursor into ......
end close @mycursor
deallocate @mycursor
......