如何在SQL Server视图中表示动态查询

Question

我有以下SQL代码：

DECLARE @i INT = 1;
DECLARE @sql_code varchar(max) = '';
DECLARE @repeats INT = 4;
WHILE @i <= @repeats
BEGIN
SET @sql_code = @sql_code+'SELECT ''foo'+cast(@i as varchar)+''' as bar UNION ALL '

SET @i = @i + 1
END;
SET @sql_code = LEFT(@sql_code,LEN(@sql_code) - 10)
exec (@sql_code)

，当在SSMS中运行时会产生以下结果：

bar
----
foo1
foo2
foo3
foo4

如何(动态)再现与视图相同的结果？

我知道不能在视图中使用声明，但可以通过存储过程或函数来实现吗？

Answer 1

您不能在视图中使用动态sql。但是，是的，您可以创建表值的用户定义函数，如本文所述。

帖子链接：https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3cdeda6c-af19-46e9-b89f-e575fecd475b/dynamic-query-in-view?forum=transactsql

加文·坎贝尔的回答应该让你知道可以做什么。

注意：有关表值用户定义函数的更多信息：请访问此文档：https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191165(v=sql.105)？重定向自=MSDN

Answer 2

事实上，不管Utsav的好答案是什么，只要你下定决心，你什么都可以做。

虽然通常不建议这样做(此答案仅供参考)，但在某些用例中，在视图中使用OPENQUERY()是有意义的。OPENQUERY()允许您对远程或本地SQL Server执行原始SQL。无论是在原始SQL本身中，还是可能在存储过程中组织得更紧密，您可以运行的查询基本上没有限制，包括动态SQL。

示例：

CREATE PROCEDURE dbo.RunSomeDynamicSQL
AS
DECLARE @DynamicSQL NVARCHAR(MAX) =
'
SELECT 1 AS Foobar;
';
EXEC sp_executesql @DynamicSQL;
GO
CREATE VIEW dbo.SomeViewThatExecutesDynamicSQL
AS
SELECT Foobar
FROM OPENQUERY
(
LocalServerName,
'
EXEC YourDatabaseName.dbo.RunSomeDynamicSQL
WITH RESULT SETS
((
Foobar INT
));
'
);
GO
SELECT Foobar
FROM dbo.SomeViewThatExecutesDynamicSQL;

您会注意到，在OPENQUERY()中执行过程时，我使用了WITH RESULT SETS关键字。这是因为OPENQUERY()需要从执行的查询中知道结果集的形状。这是在执行过程时描述这一点的一种方式。

在视图中使用OPENQUERY()的一个用例是，您可以最大限度地优化查询(例如在存储过程中)的性能，而不会失去数据库对象的可使用性。

---

使用OPENQUERY()的一个重要事实是，SQL Server引擎总是估计结果的基数为10000行。这意味着，如果您的结果集远远大于10000行，例如1000万行，那么您可能无法获得为查询服务的最佳执行计划。

Answer 3

此外，尽管我给出了信息性的答案，但如果存储过程足以满足您的用例，那么您当然可以单独使用存储过程。

可以通过存储过程完成吗

当然，只需在代码周围包装一个存储过程：

CREATE PROCEDURE dbo.SomeStoredProcedure
AS
DECLARE @i INT = 1;
DECLARE @sql_code NVARCHAR(MAX) = '';
DECLARE @repeats INT = 4;
WHILE @i <= @repeats
BEGIN
SET @sql_code = @sql_code+'SELECT ''foo'+cast(@i as varchar)+''' as bar UNION ALL '

SET @i = @i + 1
END;
SET @sql_code = LEFT(@sql_code,LEN(@sql_code) - 10)
EXEC sp_executesql @sql_code

注意，我将代码的最后一行更改为使用sp_executesql，因为它可以最大限度地降低SQL注入问题的风险。您应该始终将该过程用于动态SQL执行，而不是直接执行SQL字符串。