Tricks on how to execute a line inside a function on an Sql server

Question

FunctionX Procedure Line 345

Improper use of the lateral operator "EXECUTE STRING" within a function.

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

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

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

Thank you very much in advance.

In addition, I also need to delete the function inside. I know this contradicts the definition of functions, but I wonder if there are any tricks that can be used

Answer 1

There are no tricks, see Curse and blessings of dynamic SQL

Dynamic SQL in custom functions

It is very simple: you cannot use dynamic SQL from a given function written in T-SQL . This is because you are not allowed to do anything in UDF that can change the state of the database (because UDF can be called as part of the request). Since you can do anything from dynamic SQL, including updates, it is obvious why dynamic SQL is not allowed.

I saw more than one post in newsgroups where people were banging their heads about it. But if you want to use dynamic SQL in UDF, open and repeat your design. You are at the checkpoint and there is no way out in SQL 2000.

In SQL 2005 and later, you can implement your function as a CLR function. Recall that all data access from the CLR is dynamic SQL. (You are well protected, so if you perform an update operation with your function, you will be caught.) A word of caution though: data access from scalar UDFs can often lead to performance problems. if you say

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

and MyUdf accesses the data, you more or less created a hidden cursor.