Sep 4, 2013

Function '' is not allowed in the clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. This is because the function performs user or system data access, or is assumed to perform this access. By default, a function is assumed to perform data access if it is not schema-bound.


Suppose we have created Employee table in current database. Now we are creating another table tblData:

CREATE TABLE tbldata(
     Data VARCHAR(100),
     IsInteger BIT
)

Creating user defined function  IsInteger:

CREATE FUNCTION IsInteger(
     @Data VARCHAR(2000)
)
RETURNS BIT
AS
BEGIN
    
     DECLARE @IsNumeric BIT
    
     IF @Data NOT LIKE '%[^0-9]%'
          SET @IsNumeric = 1
     ELSE
          SET @IsNumeric = 0
         
     RETURN @IsNumeric
    
END

If we will execute following sql query:

INSERT INTO tbldata
SELECT  Name, dbo.IsInteger(Name)
FROM(
     DELETE TOP(1)
     FROM Employee
     OUTPUT DELETED.Name
) Temp

We will get error message :

Function '' is not allowed in the clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. This is because the function performs user or system data access, or is assumed to perform this access. By default, a function is assumed to perform data access if it is not schema-bound.

Cause: We cannot use user defined function in the columns from nested FROM clause. If we want to use then user defined function must be schemabound.

Solution:

Step 1: Drop the IsInteger function

DROP FUNCTION dbo.IsInteger

Step 2: Create user defined function IsInteger with schemabound.

CREATE FUNCTION IsInteger(
     @Data VARCHAR(2000)
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
    
     DECLARE @IsNumeric BIT
    
     IF @Data NOT LIKE '%[^0-9]%'
          SET @IsNumeric = 1
     ELSE
          SET @IsNumeric = 0
         
     RETURN @IsNumeric
    
END

Step 3: Execute same sql query. Now it will exeute fine.

INSERT INTO tbldata
SELECT  Name, dbo.IsInteger(Name)
FROM(
     DELETE TOP(1)
     FROM Employee
     OUTPUT DELETED.Name
) Temp

Function '' is not allowed in the OUTPUT clause, because it performs user or system data access, or is assumed to perform this access. A function is assumed by default to perform data access if it is not schemabound.


Suppose we have created Employee table in current database. If will execute following sql query in sql server:

DELETE TOP(1)
FROM Employee
OUTPUT dbo.IsInteger(DELETED.Name)

Where IsInteger is user defined function:

CREATE FUNCTION IsInteger(
    @Data VARCHAR(2000)
)
RETURNS BIT
AS
BEGIN
   
    DECLARE @IsNumeric BIT
   
    IF @Data NOT LIKE '%[^0-9]%'
         SET @IsNumeric = 1
    ELSE
         SET @IsNumeric = 0
        
    RETURN @IsNumeric
   
END

We will get error message :

Function '' is not allowed in the OUTPUT clause, because it performs user or system data access, or is assumed to perform this access. A function is assumed by default to perform data access if it is not schemabound.

Cause: We cannot use user defined function in output clause. If we want to use then user defined function must be schemabound.

Solution:

Step 1: Drop the IsInteger function

DROP FUNCTION dbo.IsInteger

Step 2: Create user defined function IsInteger with schemabound.

CREATE FUNCTION IsInteger(
    @Data VARCHAR(2000)
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
   
    DECLARE @IsNumeric BIT
   
    IF @Data NOT LIKE '%[^0-9]%'
         SET @IsNumeric = 1
    ELSE
         SET @IsNumeric = 0
        
    RETURN @IsNumeric
   
END

Step 3: Execute same sql query. Now it will execute fine.

DELETE TOP(1)
FROM Employee
OUTPUT dbo.IsInteger(DELETED.Name)