Sep 4, 2013

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)

No comments:

Post a Comment