9

Procedure FunctionX, Line 345

Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.

I get the above error when I execute a dynamic statement inside a function in SQL Server 2012. Is there a workaround for this? Any tricks?

PS: The sproc (stored procedure) is much too lengthy for its body to be inserted as-is inside the function.

DECLARE @execsql NVARCHAR(2000)
Set @execsql = 'INSERT INTO @TABLE1 EXEC SPROC1 ' + @ID_COMPANY + ',' + @ID_COUNTRY 
exec (@execsql)

Many thanks in advance.

Also, I need to be able to delete inside the function as well. I know this contradicts the definition of functions but I am wondering if there are some tricks that can be used

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
JD123456
  • 147
  • 1
  • 3
  • 8
  • 1
    The bigger question is why do you think you need to use a function here? A function is designed to return a single value, it is not a stored procedure which is what you are describing. – Sean Lange Sep 30 '15 at 15:12

2 Answers2

13

No there are no tricks, see The Curse and Blessings of Dynamic SQL

Dynamic SQL in User-Defined Functions

This is very simple: you cannot use dynamic SQL from used-defined functions written in T-SQL. This is because you are not permitted to do anything in a UDF that could change the database state (as the UDF may be invoked as part of a query). Since you can do anything from dynamic SQL, including updates, it is obvious why dynamic SQL is not permitted.

I've seen more than one post on the newsgroups where people have been banging their head against this. But if you want to use dynamic SQL in a UDF, back out and redo your design. You have hit a roadblock, and in SQL 2000 there is no way out.

In SQL 2005 and later, you could implement your function as a CLR function. Recall that all data access from the CLR is dynamic SQL. (You are safe-guarded, so that if you perform an update operation from your function, you will get caught.) A word of warning though: data access from scalar UDFs can often give performance problems. If you say

SELECT ... FROM tbl WHERE dbo.MyUdf(somecol) = @value

and MyUdf performs data access, you have more or less created a hidden cursor.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

I was having this same problem with dynamic OPENQUERY statements inside a multi-line table-valued function. SQL Server is trying to prevent users with only db_datareader access, who can select from these functions, from performing SQL injections. Long story short, remove as many single quotes as you can and find a way to do the same thing without using EXEC.

Instead of doing this:

Set @execsql = 'INSERT INTO @TABLE1 EXEC SPROC1 ' + @ID_COMPANY + ',' + @ID_COUNTRY

Do something like this:

INSERT INTO @TABLE1
SELECT *
FROM --some unfiltered version of the table your stored procedure uses
WHERE company = @ID_COMPANY
AND country = @ID_COUNTRY

Since you're calling a function from a stored procedure you can already be sure the table will be up to date. In my case, I was able to have a job refresh the function's underlying table using the stored procedure once every morning. You could also use a trigger to do that.

Dustin Morrison
  • 97
  • 2
  • 4