Sep 18, 2013

The feature 'table-valued parameters' is not supported with natively compiled stored procedures.

If we will pass the table valued variable as parameter of natively compiled stored procedures in sql server 2014. For example:

CREATE TYPE tblEmpType AS TABLE(
      ntEmpID BIGINT NOT NULL PRIMARY KEY  NONCLUSTERED HASH  WITH (BUCKET_COUNT = 1024),
    vcName VARCHAR(200),
      moSalary MONEY
) WITH (MEMORY_OPTIMIZED = ON)

GO

CREATE PROCEDURE uspGetEmployee(
      @tblEmp AS tblEmpType READONLY
)
WITHNATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)

      INSERT INTOdbo.tblEmployee
      SELECT ntEmpID,vcName,moSalary FROM dbo.tblEmployee 

END

We may get error messge like:
Msg 10772, Level 16, State 22, Procedure uspGetEmployee, Line 20
The feature 'table-valued parameters' is not supported with natively compiled stored procedures.

Cause: Sql server 2014 natively compiled stored procedure doesn't support table variable as parameters.

Solution: Use scalar variable instead of this and insert one row at a time. For example:

CREATE PROCEDURE uspGetEmployee(
      @ntEmpID BIGINT NOT NULL,
      @vcName VARCHAR(200),
      @moSalary MONEY
)
WITHNATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
      INSERT INTOdbo.tblEmployee
      SELECT @ntEmpID,@vcName,@moSalary FROM dbo.tblEmployee 
     
END

No comments:

Post a Comment