Sep 26, 2013

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

If we will pass the table variable in the natively complied stored procedure in the sql server 2014:

--Creating memeory otimized type
CREATE TYPEtblEmp AS TABLE (
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

--Creating memeory optimized table
CREATE TABLE[dbo].[tblEmp]
(
     [ntEmpID] [bigint] NOT NULL,
     [vcName] [varchar](50) NULL,
     [moSalary] [money] NULL

 PRIMARY KEY NONCLUSTERED HASH
(
     [ntEmpID]
)WITH ( BUCKET_COUNT= 1024)
)WITH ( MEMORY_OPTIMIZED= ON , DURABILITY = SCHEMA_AND_DATA )


--Passing table varaible in the nativly compiled stored procedure
CREATE PROCEDURE  getEmp(
     @tblEmp AS tblEmpType READONLY
)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
     INSERT INTO [tblEmp]
     SELECT * FROM @tblEmp
END

We may get following error message:

Msg 10772, Level 16, State 22, Procedure getEmp, Line 33
The feature 'table-valued parameters' is not supported with natively compiled stored procedures.

Cause: Natively complied stored procedures in the sql server 2014 doesn’t supports table variable as a parameter.

Solution: No need to use natively complied stored procedure to insert in the memory optimized table.

--Creating a table type
CREATE TYPEtblEmpType AS TABLE (
     ntEmpID BIGINT NOT NULL PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)

Go

--Passing table variable as parameter to insert in the
--memory optimized tables
CREATE PROCEDUREuspAddEmp(
     @tblEmp AS tblEmpType READONLY
)
AS
BEGIN

     INSERT INTO [tblEmp]
     SELECT * FROM @tblEmp

END

No comments:

Post a Comment