INSERT EXEC with Local Table Variable in Function - by JediSQL

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
1
Sign in
to vote
ID 511080 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/16/2009 3:07:18 PM
Access Restriction Public

Description

Outside of a function, I am able to INSERT EXEC into a table variable:

  declare @w int
  declare @dir table (row int);
  insert @dir (row)
  exec (N'select 1 asdf union all select 2');
  select @w = COUNT(*) from @dir;
  print @w

According to "SQL Server 2008 Books Online (April 2009)" the "INSERT (Transact-SQL)" topic (ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/1054c76e-0fd5-4131-8c07-a6c5d024af50.htm):

The only target limitation on INSERT EXEC that that "You cannot specify a table-valued parameter as the target of an INSERT EXEC statement..."

According to the "CREATE FUNCTION (Transact-SQL)" topic (ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/864b393f-225f-4895-8c8d-4db59ea60032.htm):
function_body 
Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function. 

It seems to me that this function produces no side effects since the table variable is local to the function body:

  create function dbo.aaaaa () returns int as begin
    declare @w int

    declare @dir table (row int);
    insert @dir (row)
    exec (N'select 1 asdf union all select 2');
    
    select @w = COUNT(*) from @dir;
    return @w;
    
  end
  go

However, when I try to run this create statement, I get this error:

Msg 443, Level 16, State 14, Procedure aaaaa, Line 5
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

It seems that someone got carried away with the validation code for parsing "create function" statements.  It just blanket rejects any 'INSERT EXEC' code without evaluating whether it really has side effects.


Sign in to post a comment.
Posted by JediSQL on 12/24/2013 at 11:18 AM
I guess Books Online should be updated with a statement some thing like this: "The EXEC statement will be considered to have the potential to produce side effects."
Posted by Thomas W Marshall on 12/24/2013 at 6:19 AM
While I understand that this is not a bug but a limitation imposed by the need to parse a string of arbitrary length inside a string being parsed and this intimates a recursion which would require an imposed finitude of nesting, it sure would be nice to execute a stored procedure into a temp table and build a dynamic sql statement based on a passed parameter predicate (e.g. select * from side_effected_function_derived_from_a_table_returning_stored_procedure('where this_column = ''someValue''))
Posted by Jim [MSFT] on 11/23/2009 at 10:15 AM
Hi,

Thankyou for this question. As the comment points out, the dynamic SQL is what causes the problem. Whilst parsing the code, SQL-Server would need to parse the embedded SQL :

(N'select 1 asdf union all select 2')

in order to infer it is not side-effecting. This would require a "parser within the parser". It's certainly do-able - and worthwhile for other similar scenarios (such as detecting SQL injection threats). And we have, in the past, prototyped in this area, using advanced compiler analyses. However, it's not supported in today's SQL-Server.

The BOL note that you include:

"... a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function"

leaves something unsaid: it's not enough that there is no side-effect; the parser must be able to analyze and confirm with 100% confidence, that there is no side-effect! And, with dynamic SQL, it doesn't.

So I'll close this as "by-design". But look to the future where our parsing analysis might dig deeper than it currently does.

Thanks,

Jim Hogg
Posted by Paul White NZ on 11/19/2009 at 6:34 PM
It is the dynamic SQL that is not allowed. I imagine this restriction exists because of the complexity involved in parsing an arbitrary string to determine whether it does or does not contain side-effecting T-SQL ;c)