Oct 1, 2013

The SCHEMABINDING option is supported only for natively compiled stored procedures, and is required for those stored procedures

If we will create a very simple natively compiled stored procedure in sql server 2014:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION
AS
     SELECT 'Simple Procedure'

We may get following error message:

Msg 10796, Level 15, State 1, Procedure uspGetEmployee, Line 29
The SCHEMABINDING option is supported only for natively compiled stored procedures, and is required for those stored procedures.

Cause: It is necessary that a natively complied stored procedure must have created with SCHEMABINDING option.

Solution: SCHEMABINDING option ensures no one can drop objects on which natively compiled stored procedure depends. So we should specify schema binding option. For example:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
    
     SELECT 'Simple Procedure'

END

No comments:

Post a Comment