sql-server - "Could not complete cursor operation because the set options have changed since the cursor was declared"错误

Question

我们在 SqlServer 数据库上有以下存储过程。它适用于早期版本。但它不适用于最新版本(SqlServer Express 2012)。该程序编译正常。但是在运行时它会抛出错误消息。

我对在 SqlServer 上编写存储过程几乎一无所知。这是 10 多年前创建的程序，并且一直有效。 (但现在没有)。

有什么我需要更改以纠正它的想法吗？

 CREATE PROCEDURE [GETBOUND] @mTableName char(20), @FIELD_NAME CHAR(20), @FIELD_VALUE CHAR(50), @DIRECTION CHAR(1), @nRec int , @ADD_COND CHAR(100) AS
  declare @myresult char(50)
  declare @counter numeric
  declare @mDesc char(5)
  declare @mEval char(2)


  if @Direction = 'L' BEGIN
    SELECT @mDesc = ' DESC'
    SELECT @mEval = '<='
  END ELSE BEGIN
    SELECT @mDesc = ''
    SELECT @mEval = '>='
  END


  exec('DECLARE THESQL   CURSOR FOR SELECT '+@field_name+' FROM '+@mTableName+'  WHERE '+@ADD_COND+@field_name+@mEval+''''+ @Field_Value+''' order by '+@FIELD_NAME+@mDesc)
  SET ROWCOUNT @nrec

   open THESQL


     fetch THESQL into @myresult

  select @COUNTER = 1

  WHILE (@COUNTER <@nRec)
  BEGIN
     fetch THESQL into @myresult
      SELECT @COUNTER = @COUNTER +1
  END


   close THESQL
  deallocate THESQL
 select @myresult

Answer 1

我冒昧地重写了您的存储过程。你根本不需要 CURSOR 最后你只需要从游标返回最后一个元素。为此，我只是颠倒了逻辑，所以现在它将返回没有光标的 TOP 1 记录。

此外，我重命名了一些变量，使它们成为 varchar 而不是 char 并使它们更大。我的大多数表格都超过 20 个字符。

我删除了不再使用的变量。

EXEC [ups_GetBound]
    @pTable_Name = 'tables'
   ,@pField_Name = 'name'
   ,@pField_Value = 'MyTable'
   ,@pDirection = 'M'
   ,@pExtra_Condition = ''
   ,@pCounter = 13;


CREATE PROC [ups_GetBound]
    (
     @pTable_Name SYSNAME
    ,@pField_Name SYSNAME
    ,@pField_Value VARCHAR(50)
    ,@pDirection CHAR(1)
    ,@pExtra_Condition VARCHAR(100)
    ,@pCounter INT 
    )
AS
    BEGIN
        DECLARE @mDesc CHAR(5)
           ,@mEval CHAR(2)
           ,@Extra_Condition NVARCHAR(100) = '';

        IF @pDirection = 'L'
            BEGIN
                SELECT @mDesc = ' DESC'
                SELECT @mEval = '<='
            END
        ELSE
            BEGIN
                SELECT @mDesc = ' '
                SELECT @mEval = '>='
            END

        DECLARE @sql NVARCHAR(500)
           ,@ParamDefenition NVARCHAR(250);

        SET @ParamDefenition = N'@pField_Value VARCHAR(50),@pCounter INT'

        SET @sql = N'WITH CounterSelect as (SELECT ' + QUOTENAME(@pField_Name) 
            + ', ROW_NUMBER() OVER (ORDER BY '+ QUOTENAME(@pField_Name) + @mDesc + ') as RowNum' 
            + ' FROM ' + QUOTENAME(@pTable_Name) 
            + ' WHERE '+ @pExtra_Condition + QUOTENAME(@pField_Name) + @mEval + '@pField_Value )' 
            + ' SELECT ' + QUOTENAME(@pField_Name) 
            + ' From CounterSelect WHERE RowNum = @pCounter'

        --PRINT @sql
        EXEC sp_executesql
            @sql
           ,@ParamDefenition
           ,@pField_Value
           ,@pCounter
    END