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