Sep 18, 2013

The feature 'cursors' is not supported with natively compiled stored procedures.

If we create following natively compiled stored procedures with cursor in sql server 2014:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGINATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
     DECLARE
          @ntEmpID BIGINT,
          @vcName VARCHAR(200),
          @moSalary MONEY
    
     DECLARE curEmp CURSOR FOR SELECT ntEmpID,vcName,moSalary FROMdbo.tblEmployee
     OPEN curEmp

     FETCH NEXTFROM curEmp INTO@ntEmpID,@vcName,@moSalary

     WHILE @@FETCH_STATUS= 0 BEGIN

          UPDATE dbo.tblEmployee SET
              moSalary = @moSalary + 100
          WHERE ntEmpID = @ntEmpID

          FETCH NEXT FROM curEmp INTO @ntEmpID,@vcName,@moSalary

     END
END

We may get error messages something like this:

Msg 10772, Level 16, State 25, Procedure uspGetEmployee, Line 80
The feature 'cursors' is not supported with natively compiled stored procedures.
Msg 10772, Level 16, State 102, Procedure uspGetEmployee, Line 81
The statement 'OPEN CURSOR' is not supported with natively compiled stored procedures.
Msg 10772, Level 16, State 102, Procedure uspGetEmployee, Line 83
The statement 'FETCH CURSOR' is not supported with natively compiled stored procedures.
Msg 10772, Level 16, State 99, Procedure uspGetEmployee, Line 85
The function '@@fetch_status' is not supported with natively compiled stored procedures.
Msg 10772, Level 16, State 102, Procedure uspGetEmployee, Line 91
The statement 'FETCH CURSOR' is not supported with natively compiled stored procedures.

Cause: Sql server 2014 compiled stored procedures doesn’t support curosr.

Solution: Write equivalent sql script using while loop. For example:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGINATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
     DECLARE
          @ntEmpID BIGINT,
          @vcName VARCHAR(200),
          @moSalary MONEY
    
     SELECT  TOP(1)
          @ntEmpID = ntEmpID,
          @vcName = vcName,
          @moSalary = moSalary
     FROM dbo.tblEmployee
     ORDER BYntEmpID ASC

     WHILE @ntEmpID IS NOT NULL  BEGIN

          UPDATE dbo.tblEmployee SET
              moSalary = @moSalary + 100
          WHERE ntEmpID = @ntEmpID

          SELECT TOP(1)
              @ntEmpID = ntEmpID,
              @vcName = vcName,
              @moSalary = moSalary
          FROM dbo.tblEmployee
          WHERE ntEmpID > @ntEmpID
          ORDER BY ntEmpID ASC

     END


END

No comments:

Post a Comment