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

Question

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?

I'm not sure you can execute stored procedures inside a function. If I'm correct, it's not the insert, it's the `exec` itself. — Zohar Peled, May 09 '17 at 07:53
Well, it turns out I'm probably correct, [as this SO post suggests](http://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function) — Zohar Peled, May 09 '17 at 07:56
@ZoharPeled you can execute some extended procs from functions. The answer you link executes `master..xp_cmdshell`. — Martin Smith, May 09 '17 at 08:17
@MartinSmith Yes, I've seen it. I've also seen it's highly unrecommended. — Zohar Peled, May 09 '17 at 08:37
@ZoharPeled I wasn't recommending it just pointing out that your initial comment isn't always true. — Martin Smith, May 09 '17 at 18:33

score 6 · Accepted Answer · edited Nov 13 '18 at 23:02

6

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.

edited Nov 13 '18 at 23:02

Community

1
1

answered May 09 '17 at 08:15

Martin Smith

438,706
87
741
845

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

1 Answers1