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