Sep 18, 2013

The feature 'inline table variables' is not supported with natively compiled stored procedures

Sql server 2014 natively compiled stored procedures doesn't supports inline table variable. For example if we will execute following sql script:

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

     DECLARE @tblEmpType AS TABLE(
          ntEmpID BIGINT,
          vcName VARCHAR(200),
          moSalary MONEY
     )

     INSERT INTO@tblEmpType
     SELECT 1,'Scott',500

END

We may get error message something like this:
Msg 10772, Level 16, State 24, Procedure uspGetEmployee, Line 44
The feature 'inline table variables' is not supported with natively compiled stored procedures.

Cause: Sql server 2014 natively compiled stored procedures doesn't supports inline table variable.

Solution: Instead of inline table variable use table type to declare table variable. For example:
First create table type:

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

Creating stored procedure:

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

     DECLARE@tblEmpType AS dbo.tblEmpType

     INSERT INTO @tblEmpType
     SELECT 1,'Scott',500

END

No comments:

Post a Comment