在 SQL Server 2008 中创建参数化 VIEW
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4498364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create parameterized VIEW in SQL Server 2008
提问by Sreekumar P
Can we create parameterized VIEW in SQL Server 2008.
我们可以在 SQL Server 2008 中创建参数化的 VIEW。
Or Any other alternative for this ?
或者还有其他选择吗？
回答by AdaTheDev
Try creating an inline table-valued function. Example:
尝试创建一个内联表值函数。例子：
CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
RETURNS TABLE
AS
RETURN
(
SELECT Field1, Field2
FROM SomeTable
WHERE Field3 = @Parameter1
)
-- Then call like this, just as if it's a table/view just with a parameter
SELECT * FROM dbo.fxnExample(1)
If you view the execution plan for the SELECT you will not see a mention of the function at all and will actually just show you the underlying tables being queried. This is good as it means statistics on the underlying tables will be used when generating an execution plan for the query.
如果您查看 SELECT 的执行计划，您根本不会看到该函数的提及，而实际上只会向您显示正在查询的基础表。这很好，因为这意味着在为查询生成执行计划时将使用基础表的统计信息。
The thing to avoid would be a multi-statement table valued function as underlying table statistics will not be used and can result in poor performance due to a poor execution plan.
Example of what to avoid:
要避免的是多语句表值函数，因为不会使用底层表统计信息，并且可能由于执行计划不佳而导致性能不佳。避免的
例子：
CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
RETURNS @Results TABLE(Field1 VARCHAR(10), Field2 VARCHAR(10))
AS
BEGIN
INSERT @Results
SELECT Field1, Field2
FROM SomeTable
WHERE Field3 = @Parameter1
RETURN
END
Subtly different, but with potentially big differences in performance when the function is used in a query.
略有不同，但在查询中使用该函数时，性能可能会有很大差异。
回答by Adriaan Stander
No, you cannot. But you can create a user defined table function.
你不能。但是您可以创建用户定义的表函数。
回答by heximal
in fact there exists one trick:
事实上存在一个技巧：
create view view_test as
select
*
from
table
where id = (select convert(int, convert(binary(4), context_info)) from master.dbo.sysprocesses
where
spid = @@spid)
... in sql-query:
...在 sql 查询中：
set context_info 2
select * from view_test
will be the same with
将与
select * from table where id = 2
but using udf is more acceptable
但使用 udf 更容易接受
回答by davek
As astander has mentioned, you can do that with a UDF. However, for large sets using a scalarfunction (as oppoosed to a inline-table function) the performance will stink as the function is evaluated row-by-row. As an alternative, you could expose the same results via a stored procedure executing a fixed query with placeholders which substitutes in your parameter values.
正如旁观者所提到的，您可以使用 UDF 来做到这一点。但是，对于使用标量函数的大型集合（与内联表函数相反），由于函数是逐行计算的，因此性能会很差。作为替代方案，您可以通过执行固定查询的存储过程公开相同的结果，该查询使用占位符替换您的参数值。
(Here'sa somewhat dated but still relevant article on row-by-row processing for scalar UDFs.)
（这是一篇关于标量 UDF 的逐行处理的有点过时但仍然相关的文章。）
Edit:comments re. degrading performance adjusted to make it clear this applies to scalar UDFs.
编辑：评论重新。降低性能进行了调整，以明确这适用于标量 UDF。
回答by Pankaj Agarwal
no. You can use UDF in which you can pass parameters.
不。您可以使用 UDF，您可以在其中传递参数。
