请教SQL关于游标的问题

悬赏园豆：20 [已解决问题] 解决于 2012-11-12 16:12

存储过程有个参数，根据这个参数来分别写的游标查询语句，但是处理这个游标是同一个代码，好像不能同时定义两次，我又不想把后续的处理代码写两次，有什么办法吗？

declare @tempid int,@lasttime datetime,@sql nvarchar(1000)
set @tempid=0;
BEGIN
    SET NOCOUNT ON;
if @para1='oneself'
    begin
        declare bzrid cursor for select id from erpzjbzrlist as a        
    end
else if @para1='all'
    begin
        declare bzrid cursor for select id from erpzjbzrlist as a where (select max(endtime) from erpzjphasetime where bianhao=a.id  group by bianhao) >=getdate();    
    end
else 
    begin
       return -1
    end
    open bzrid
    fetch next from bzrid  into @tempid
    while (@@fetch_status=0)
    begin
        select @lasttime =max(endtime) from erpzjphasetime where bianhao=(select id from erpzjbzrlist as a where (select max(endtime) from erpzjphasetime where bianhao=a.id  group by bianhao) >=getdate() and id<@tempid) group by bianhao
    if(@lasttime<>null)
    begin
        select @lasttime=dateadd(day,1,@lasttime);
        exec ERP_p_adjustProjectTime @tempid,@lasttime;
    end
    fetch next from bzrid  into @tempid
    end

SQL Server 数据库

happydaily | 菜鸟二级 | 园豆：260
提问于：2012-11-12 15:45

< >

最佳答案

请参考下述示例代码:

SELECT  'a' AS id
INTO    #a
SELECT  'b' AS id
INTO    #b

DECLARE @p VARCHAR(50)
DECLARE @cs1 CURSOR

IF 1 = 2 
    SET @cs1= CURSOR
FOR
    SELECT  id
    FROM    #a
ELSE 
    SET 
@cs1= CURSOR
FOR
    SELECT  id
    FROM    #b  
    
OPEN @cs1
FETCH NEXT FROM @cs1 INTO @p
WHILE @@Fetch_Status = 0 
    BEGIN
        SELECT @p
        FETCH NEXT FROM @cs1 INTO @p
    END
CLOSE @cs1  
DEALLOCATE @cs1

收获园豆：20

夏狼哉 | 小虾三级 |园豆：833 | 2012-11-12 16:03

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

请教SQL关于游标的问题

欢迎，请先登录或者注册。