视图中具有变量的动态SQL（SQL Server） [英] Dynamic SQL with variables inside a view (SQL Server)

问题描述

您好，我基本上是想在SQL Server 2008中的新视图窗口中执行此操作：

Hello I'm essentially trying to do this inside a new view window in SQL Server 2008:

declare @var =（从数据库中选择db）；
exec（'从'+ @var'+ .dbo.Names中选择名称）;

declare @var = (select db from databases); exec ('select name from ' + @var ' + .dbo.Names);

此视图实际上在SQL Server中运行，但我无法保存它（它给我一个错误），我可能会创建一个表返回函数，在其中做所有这些相同的事情，然后返回表并创建一个视图，该视图基本上从该表中获取所有内容，但我不确定会发生性能下降从这样做。任何建议将不胜感激！谢谢。

This view actually runs in SQL Server but I cannot save it (it gives me an error), I could potentially just create a table returning function, do all of this same stuff in it and return the table and create a view that basically takes everything from that table but I was unsure of performance hits that could occur from doing this. Any suggestions would be greatly appreciated! Thanks.

解决方案：
我最终让它删除了旧视图并在存储过程中重新创建了一个新视图（使用动态sql）。更改该值后，我将仅调用SP，它将更新视图以指向正确的数据库。感谢所有帮助人员，知道无法解决的事情使我无法尝试这些方法。

Solution: I just ended up having it drop the old view and recreate a new view (using dynamic sql) in a Stored Procedure. When that value is changed I will just call the SP which will update the views to point to the correct databases. Thanks for all the help guys, knowing what can't be done stopped me from trying those methods.

推荐答案

视图不能接受参数。表值函数就是解决方案。但是，您至少必须知道将要出现在另一端的表和结果集。如果您将要查询的表作为参数传递，您如何知道结果数据集的结构？

View's cannot accept parameters. A table valued function IS the solution. But you have to at least know the table and result set that is going to come out the other end. If your passing the table to be queried as a parameter how do you know the structure of the resulting data set?

这篇关于视图中具有变量的动态SQL（SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！