Sep 18, 2013

The feature 'multi-row table-valued constructor' is not yet implemented with natively compiled stored procedures.

In sql server 2014 natively compiled stored procedures, if we will try to create following stored procedure:

CREATE PROCEDURE[dbo].[uspGetEmployee](
     @ntEmpID AS BIGINT
)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATIONLEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)

     INSERT INTO dbo.tblEmployee (ntEmpID,vcName,moSalary )
     VALUES(1,'Scott',500),(2,'Greg',800)

END

We may get error message like:

Msg 10773, Level 16, State 37, Procedure uspGetEmployee, Line 11
The feature 'multi-row table-valued constructor' is not yet implemented with natively compiled stored procedures.

Cause: sql server 2014 natively compiled stored procedures don't support multi row insertion and also doesn't support UNION ALL.

Solution: We will have to write multiple insert statements. For example:

CREATE PROCEDURE[dbo].[uspGetEmployee](
     @ntEmpID AS BIGINT
)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGINATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)

     INSERT INTOdbo.tblEmployee (ntEmpID,vcName,moSalary )
     VALUES(1,'Scott',500)
    
     INSERT INTOdbo.tblEmployee (ntEmpID,vcName,moSalary )
     VALUES(2,'Greg',800)


END

No comments:

Post a Comment