SQL脚本游标、变量和NPoco

Question

我有一个包含声明为DECLARE @@var_1 as bigint的变量的SQL脚本，它包含一个while循环作为WHILE @@FETCH_STATUS，现在使用NPoco执行这样的脚本会引发Must declare the scalar variable "@FETCH_STATUS".异常，我该如何解决它？主要目的是在SQL Server和ORACLE中使用相同的脚本。

DECLARE @@LayerId bigint;
DECLARE @@DId as bigint;
DECLARE @@DataSegment as CURSOR;
DECLARE @@IterationNo as int;

IF OBJECT_ID(N'DataSegment') IS NOT NULL AND
   OBJECT_ID(N'Layer') IS NOT NULL
BEGIN
    SET @@IterationNo = 0;
    SET @@DataSegment = CURSOR FORWARD_ONLY FOR
    SELECT Id FROM DataSegment  

    OPEN @@DataSegment;
    FETCH NEXT FROM @@DataSegment INTO @@DId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET  @@IterationNo = @@IterationNo + 1;
        SET  @@LayerId = 9;
        PRINT @@LayerId;


    --Insert parent RouteVersion
        INSERT INTO Layer Values(@@LayerId,'Migration',6,'Trace',
        'Route','TEST', @@DId, NULL, @@LayerId, 9)   
             Print 'Iter is ' ;
        Print @@IterationNo

     --fetch next
         FETCH NEXT FROM @@DataSegment INTO @@DId;
    END
    CLOSE @@DataSegment;
    DEALLOCATE @@DataSegment;

END

提前谢谢。

Answer 1

尝尝这个

DECLARE @LayerId bigint;
DECLARE @DId as bigint;
DECLARE @DataSegment as CURSOR;
DECLARE @IterationNo as int;

IF OBJECT_ID(N'DataSegment') IS NOT NULL AND
   OBJECT_ID(N'Layer') IS NOT NULL
   BEGIN
SET @IterationNo = 0;
SET @DataSegment = CURSOR FORWARD_ONLY FOR
SELECT ID FROM DataSegment    

OPEN @DataSegment
FETCH NEXT FROM @DataSegment INTO @DId

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET  @IterationNo = @IterationNo + 1;
    SET  @LayerId = 9;
    PRINT @LayerId;


Insert parent RouteVersion
    INSERT INTO Layer Values(@LayerId,'Migration',6,'Trace',
    'Route','TEST', @DId, NULL, @LayerId, 9)   
         Print 'Iter is ' ;
    Print @IterationNo

     FETCH NEXT FROM @DataSegment INTO @DId;
END
CLOSE @DataSegment;
DEALLOCATE @DataSegment;
END

或

将.net连接设置更改为

Database=db;Data Source=localhost;User Id=root;Password=pass;Allow User Variables=True

然后尝试你自己的代码

Answer 2

对于系统参数，请使用@而不是@@

DECLARE @LayerId bigint;
DECLARE @DId as bigint;
DECLARE @DataSegment as CURSOR;
DECLARE @IterationNo as int;

IF OBJECT_ID(N'DataSegment') IS NOT NULL AND
   OBJECT_ID(N'Layer') IS NOT NULL
   BEGIN
SET @IterationNo = 0;
SET @DataSegment = CURSOR FORWARD_ONLY FOR
SELECT ID FROM DataSegment    

OPEN @DataSegment
FETCH NEXT FROM @DataSegment INTO @DId

WHILE (@@@FETCH_STATUS = 0)
BEGIN
    SET  @IterationNo = @IterationNo + 1;
    SET  @LayerId = 9;
    PRINT @LayerId;


Insert parent RouteVersion
    INSERT INTO Layer Values(@LayerId,'Migration',6,'Trace',
    'Route','TEST', @DId, NULL, @LayerId, 9)   
         Print 'Iter is ' ;
    Print @IterationNo

     FETCH NEXT FROM @DataSegment INTO @DId;
END
CLOSE @DataSegment;
DEALLOCATE @DataSegment;
END