sql-server - 如何在 Sql Server 函数内执行字符串的技巧

Question

过程 FunctionX，第 345 行

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

当我在 SQL Server 2012 中的函数内执行动态语句时，出现上述错误。有解决方法吗？有什么技巧吗？

PS:存储过程(存储过程)太长，无法将其主体按原样插入函数内。

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

提前非常感谢。

此外，我还需要能够在函数内部进行删除。我知道这与函数的定义相矛盾，但我想知道是否有一些可以使用的技巧

Answer 1

不，没有任何技巧，请参阅 The Curse and Blessings of Dynamic SQL

Dynamic SQL in User-Defined Functions

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

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

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

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

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