From Jimbojw.com

In Microsoft SQL Server 2005, you can't use the EXEC() method inside a User Defined Function (UDF). If you attempt to do so, you'll end up with an error message reading:

Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

This article aims to explain why the limitation exists and what you can do about it.

Contents 1 How Did We Get Here? 2 Reason 1 - Performance 3 Reason 2 - Trust 4 Making the Easy Stuff Hard 5 Comments 6 Jaishree said ... 7 kluck said ... 8 ShipJumpa said ... 9 JC said ... 10 Jonk said ... 11 Deepak said ... 12 Maxx said ... 13 John Fuex said ...

How Did We Get Here?

A good question to start off with is, perhaps, why would one want to use EXEC inside a user function in the first place? Let's look at a trivial example.

Suppose we find ourselves checking frequently on the total number of users and we decide to whip up a user defined function to take care of this. The function might look like this:

CREATE FUNCTION CurrentUserCount
( )
RETURNS BIGINT
AS
BEGIN
    DECLARE @res BIGINT;
    SELECT @res = SELECT COUNT(*) FROM users;
    RETURN @res;
END

Note: There are better ways to get a count on the table which are outside the current discussion.

Now suppose we find we want to count more than just the users table, we want a flexible function that can count any table. We might try something like this:

CREATE FUNCTION CurrentTableCount
(@table_name VARCHAR(100) )
RETURNS BIGINT
AS
BEGIN
    DECLARE @SQL VARCHAR(200);
    DECLARE @res BIGINT;
 
    SELECT @SQL = 'select @res = select count(*) from ' + @table_name;
 
    EXEC(@SQL)
 
    RETURN @res;
END

Seems pretty straightforward, right? We're building a dynamic query based on the supplied table name, executing that query, then returning the result. To our utter dismay and disappointment however, upon executing this code we get the following error message:

 Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

Bummer. It turns out that this is a design feature of MS SQL Server, and there are two rationales which may have resulted in it's creation:

Reason 1 - Performance

Dynamic queries are not prone to automated optimization. An ad-hoc query analyzer will surely be run on every query just before execution, but static queries can be evaluated at creation time, while dynamic queries must wait until the very end.

There's an argument to be made here that given a larger user defined function (one with substantially more code), a function analyzer/query analyzer may be able to squeeze some performance out of the function at "compile time", before it is ever run.

Reason 2 - Trust

The contract for a function (user defined or otherwise) holds that the state of the database will not change as a result of executing the function. This means that no new records will be created, none will be modified, and none will be deleted. Further, no tables or databases will be created or dropped.

Microsoft holds creators of user defined functions to this standard by limiting the types of database access which are permitted inside a UDF. This protects consumers of UDF's from accidental database corruption when none was expected. One of the limitations set by MS is the disabling of EXEC for String arguments, since in theory one could put together a string that would INSERT or UPDATE a table in the database, then simply EXEC it.

The obvious counter-point to this is that the EXEC function could be made to be smart enough to understand the scope under which it was executed. So that it could behave differently when called from a UDF then when called from say, a stored procedure.

But this is a very hard thing to do since EXEC calls could be nested to any depth and thus the scoping rules would need to be recursive to be effective. The much simpler route is to turn off EXEC support at the UDF level altogether.

Making the Easy Stuff Hard

You might rightly ask, "but doesn't this make an otherwise easy task impossible?". Not quite. Research shows that at the current time UDF's can still EXEC a certain class of Stored Procedure called an Extended Stored Procedure.

The bad news is that extended stored procedures are slated for removal from future releases of SQL Server. From the MSDN2 - Extended Stored Procedures page:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR integration instead.

It goes on to say:

In earlier releases of SQL Server, extended stored procedures (XPs) provided the only mechanism that was available for database application developers to write server-side logic that was either hard to express or impossible to write in Transact-SQL. CLR Integration provides a more robust alternative to writing such stored procedures. Furthermore, with CLR Integration, logic that used to be written in the form of stored procedures is often better expressed as table-valued functions, which allow the results constructed by the function to be queried in SELECT statements by embedding them in the FROM clause.

It is likely that CLR will facilitate alternatives to many problems currently solvable with Dynamic-SQL. In the meantime, the standing advice should probably be to look for non-dynamic SQL solutions to your database implementation tasks.

Comments

Leave a comment

Sorry, comments are disabled.

Jaishree said ...

kluck said ...

ShipJumpa said ...

JC said ...

Jonk said ...

Deepak said ...

Maxx said ...

John Fuex said ...