函数中的变量表或列名
我正在尝试搜索数据库中的所有表和列，此处。建议的技术是构造 SQL 查询字符串，然后执行它们。作为存储过程，这很有效。 （变量表/列名称的另一个示例是此处。同样，EXEC 用于执行“动态 SQL”。）
但是，我的应用程序要求我在函数而不是 SP 中执行此操作。 （我们的开发框架很难从 SP 获取结果。）但是在函数中，至少在 SQL Server 2008 R2 上，您不能使用 EXEC；我收到此错误：
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
根据 这篇文章，显然是由 Microsoft 开发人员编写的，这是设计使然；它与 INSERT 无关，只是当您执行动态构造的 SQL 代码时，解析器不能保证没有副作用。因此它不允许您创建这样的函数。
那么...有什么方法可以迭代函数中的许多表/列吗？
我从 BOL 中看到
以下语句在函数中有效：...
- 执行 调用扩展存储过程的语句。
哈 - 如何保证延长的 SP 没有副作用？
但这无论如何对我没有帮助：
扩展存储过程，当它从内部调用时 函数，无法将结果集返回给客户端。任何 ODS API 返回结果集给客户端会返回FAIL。扩展存储的 过程可以连接回 SQL Server 实例；然而，它 不应尝试加入与该函数相同的事务 调用扩展存储过程。
由于我们需要函数来返回搜索结果，因此 ESP 没有帮助。
无论如何，我真的不想进入扩展的 SP：增加环境中编程语言的数量会使我们的开发环境变得更加复杂，超出了它的价值。
我现在能想到几个解决方案，但都不是很令人满意：
- 首先调用一个SP来产生所需的数据并将其放入表中，然后从仅从表中读取结果的函数中进行选择；如果搜索需要一段时间并且两个用户的搜索重叠，这可能会很麻烦。或者，
- 让应用程序（而不是函数）生成一个长查询，命名数据库中的每个表和列名称。我想知道 JDBC 驱动程序是否可以处理那么长的查询。或者，
- 让应用程序（而不是函数）生成一系列简短的查询，命名数据库中的每个表和列名称。这将使整体搜索速度变慢很多。
感谢您的任何建议。
PS 经过进一步搜索，我偶然发现了 这个问题密切相关。它没有答案。
更新：不再需要
我认为这个问题仍然有效，我们可能会再次遇到需要它的情况。但是，对于当前的问题，我不再需要答案。经过多次反复试验，我设法让我们的应用程序框架通过存储过程中的 JDBC 驱动程序从 RDBMS 检索行结果。因此让这个东西作为一个函数来工作是没有必要的。
但是，如果有人在这里发布的答案有助于解决上述问题，我将很乐意投票和/或酌情接受它。
I'm trying to search all tables and columns in a database, a la here. The suggested technique is to construct SQL query strings and then EXEC them. This works well, as a stored procedure. (Another example of variable table/column names is here. Again, EXEC is used to execute "dynamic SQL".)
However, my app requires that I do this in a function, not an SP. (Our development framework has trouble obtaining results from an SP.) But in a function, at least on SQL Server 2008 R2, you can't use EXEC; I get this error:
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
According to the answer to this post, apparently by a Microsoft developer, this is by design; it has nothing to do with the INSERT, only the fact that when you execute dynamically-constructed SQL code, the parser cannot guarantee a lack of side effects. Therefore it won't allow you to create such a function.
So... is there any way to iterate over many tables/columns within a function?
I see from BOL that
The following statements are valid in a function: ...
- EXECUTE
statements calling extended stored procedures.
Huh - How could extended SP's be guaranteed side-effect free?
But that doesn't help me anyway:
The extended stored procedure, when it is called from inside a
function, cannot return result sets to the client. Any ODS APIs that
return result sets to the client will return FAIL. The extended stored
procedure could connect back to an instance of SQL Server; however, it
should not try to join the same transaction as the function that
invoked the extended stored procedure.
Since we need the function to return the results of the search, an ESP won't help.
I don't really want to get into extended SP's anyway: incrementing the number of programming languages in the environment would complicate our development environment more than it's worth.
I can think of a few solutions right now, none of which is very satisfactory:
- First call an SP that produces the needed data and puts it in a table, then select from the function which merely reads the result from the table; this could be trouble if the search takes a while and two users' searches overlap. Or,
- Have the application (not the function) generate a long query naming every table and column name from the db. I wonder if the JDBC driver can handle a query that long. Or,
- Have the application (not the function) generate a long series of short queries naming every table and column name from the db. This will make the overall search a lot slower.
Thanks for any suggestions.
P.S. Upon further searching, I stumbled across this question which is closely related. It has no answers.
Update: No longer needed
I think this question is still valid, and we may again have a situation where we need it. However, I don't need an answer anymore for the present problem. After much trial-and-error I managed to get our application framework to retrieve row results from the RDBMS via the JDBC driver from the stored procedure. Therefore getting the thing to work as a function is unnecessary.
But if anyone posts an answer here that helps with the stated problem, I will be happy to upvote and/or accept it as appropriate.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
sp 基本上是一个带有一些附加组件的预定义 sql 语句。
所以如果你有
PSEUDOCODE
并且您不能使用 SP
然后您只需执行“Select * From MyTable”中的 SQL
至于那个 naff sql 代码。
首先，您可以使用 where 子句将表连接到列，这将逐行删除 if 内容。
问另一个问题。就像如何改进这一点一样，还有比我更多尝试的空间。
An sp is basically a predefined sql statment with some add ons.
So if you had
PSEUDOCODE
And you can't use the SP
Then you just execute the SQL as in "Select * From MyTable"
As for that naff sql code.
For start you could join table to column with a where clause, which would get rid of that line by line if stuff.
Ask another question. Like How could this be improved, there's lots of scope for more attempts than mine.