如何使用动态from子句创建存储过程

I have a problem with an application which uses the same stored procedure over and over again to populate people information in dropdown lists. The problem is that sometimes people aren't there anymore as the data changes. I have two views I can use to select from, but I want to dynamically change which view is being used based on the state of the application.

我有一个问题,一个应用程序反复使用相同的存储过程来填充下拉列表中的人员信息。问题是,随着数据的变化,人们有时不再存在。我有两个可供选择的视图,但我想根据应用程序的状态动态更改正在使用的视图。

For new records, I only want to see current people. If I'm updating an existing record, I may want to see all people since the existing record may reference someone who is not current anymore.

对于新记录,我只想看到当前的人。如果我正在更新现有记录,我可能希望看到所有人,因为现有记录可能会引用不再是当前记录的人。

How would I go about passing the view name in to my stored procedure so I can select from it?

我如何将视图名称传递给我的存储过程,以便我可以从中进行选择?

I've already tried adding:

我已经尝试过添加:

@view varchar(50)

select a, b from @view

But I get an error stating I must declare the variable @view.

但我得到一个错误,说明我必须声明变量@view。

Is this even possible to do?

这甚至可以做到吗?

7 个解决方案

#1

If you use only two views and you know their names, you could just pass a bit parameter to a procedure.

如果您只使用两个视图并且知道它们的名称,则可以将一个位参数传递给过程。

@useFirstView bit

IF @useFirstView = 1 
   -- select from firstView
ELSE
   -- select from secondView

#2

You can create your statement in the SP and call:

您可以在SP中创建语句并调用:

exec sp_executesql @sql

#3

I was wondering about the exact same thing and I came across this thread in the MSDN forums:

我想知道完全相同的事情,我在MSDN论坛中遇到了这个帖子:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/fa523f21-5a8b-421f-99ce-9bb2ec30e848

Take a look at the answer of Sami Samir in the end - at least for me it provided a perfect solution and I hope it will do the same for you.

最后看看萨米萨米尔的答案 - 至少对我来说它提供了一个完美的解决方案,我希望它能为你做同样的事情。

Dynamic SQL for a dynamic choice between a fixed number of tables/views which have the same fields just doesn't sound right - hard to debug and error/hack attack-prone.

动态SQL在固定数量的表/视图之间进行动态选择,这些表/视图具有相同的字段,听起来不对 - 难以调试和错误/黑客攻击。

All the best.

祝一切顺利。

#4

You can use dynamic SQL to solve your issue.

您可以使用动态SQL来解决您的问题。

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT a, b from FROM ' + @view
EXEC (@sqlCommand)

But I don't think that this is the best solution. You should probably have a query like

但我不认为这是最好的解决方案。你应该有一个类似的查询

If @IsActiveFlag = TRUE THEN
  SELECT * FROM People WHERE IsActive = 1
ELSE
  SELECT * FROM People
ENDIF

#5

Have you tried declaring a variable within the stored procedure and assigning the parameter as it's value?

您是否尝试在存储过程中声明变量并将参数指定为其值?

For example, assuming you have a parameter named @view:

例如,假设您有一个名为@view的参数:

declare @viewName nvarchar(50)

声明@viewName nvarchar(50)

set @viewName = @view

设置@viewName = @view

#6

You just miss the declaration of you variable

你只是错过了变量的声明

DECLARE @view VARCHAR(50)
SET @view = 'myView'
SELECT a,b FROM @view

or if used in an stored procedure

或者如果在存储过程中使用

CREATE PROCEDURE SelectFromView
{
    @view VARCHAR(50)
}
AS
BEGIN

    DECLARE @sqlCommand varchar(1000)
    SET @sqlCommand = 'SELECT a, b from FROM ' + @view
    EXEC (@sqlCommand)

END

Beware of SQLInjection attacs on this, like mentioned before.

请注意SQLInjection attacs,如前所述。

[edit] Corrected use of dynamic sql creation with the code from Nathan Koop, thx. [/edit]

[编辑]纠正了使用Nathan Koop的代码创建动态sql,thx。 [/编辑]

#7

Your idea is tempting, but this is NOT SUPPORTED (why do people here even suggest to DECLARE a variable?) or, in other words, you're doing it the wrong way.

你的想法很有吸引力,但这不是支持的(为什么这里的人甚至建议DECLARE变量?),换句话说,你做错了。

The FROM clause accepts either a table/view/function name (hardcoded, not from a varchar variable) or a table variable. A table variable looks like this:

FROM子句接受表/视图/函数名称(硬编码,而不是来自varchar变量)或表变量。表变量如下所示:

DECLARE @table TABLE (a int, b int).

Note that it is of type TABLE, not VARCHAR. After that you can do:

请注意,它的类型为TABLE,而不是VARCHAR。之后你可以这样做:

SELECT a,b FROM @table.

But obviously this is not what you want. I would suggest to use sp_executesql (only if view/table names are set by you, not coming from user data or similar) or an IF-ELSE statement.

但显然这不是你想要的。我建议使用sp_executesql(仅当您设置视图/表名称,而不是来自用户数据或类似名称)或IF-ELSE语句。

#1