CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012sql-functiontable-variable
ProfK
ProfK

Reputation: 51084

Why can't I run INSERT EXEC on a table variable in a T-SQL function?

My function looks like this:

CREATE FUNCTION fn_FileSys_DirExists(@dirName AS nvarchar(260))
RETURNS bit
AS
BEGIN
    DECLARE @dirExists int
    DECLARE @fileResults TABLE
    (
        file_exists int,
        file_is_a_directory int,
        parent_directory_exists int
    )

    INSERT @fileResults (file_exists, file_is_a_directory, parent_directory_exists)
    EXEC master.dbo.xp_fileexist @dirName

    SELECT @dirExists = file_is_a_directory FROM @fileResults
    RETURN @dirExists
END

When I try and execute the above SQL, I get the following error:

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

I thought operations on a table variable in a function weren't considered side effecting operations?

Upvotes: 5

Views: 5637

Answers (1)

Martin Smith
Martin Smith

Reputation: 453707

INSERT ... EXEC is a side effecting operator because it ends up creating a temporary table behind the scenes.

This is behind the parameter table scan shown in the execution plan for this dbfiddle

See The Hidden Costs of INSERT EXEC for more about that.

You'll be best off writing a CLR function to do this.

Upvotes: 6

Related Questions