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

No comments:

Post a Comment