在sql存储过程中传递表名 [英] Passing table name in sql stored procedure

问题描述

是否可以将表名作为输入参数传递给存储过程?

Is it possible to pass the table name as input parameter to the stored procedure?

例如:

create procedure test
@tablename char(10)
as
begin
select * from @tablename
end
go

我知道这行不通.那么如果我想将表名传递到存储过程中，最好的方法是什么?

I know this does not work. So what is the best way if I want to pass the table name into the stored procedure?

非常感谢

推荐答案

最安全的方法是通过视图.

The safest way to do this is via a view.

创建一个视图，将您可能希望访问的所有表(并且这些表都必须具有相同的列结构)联合起来，并用表名作为行的前缀.

Create a view which unions all the tables you may wish to access (and which must all have the same column structure), and prefix the rows with the table name.

CREATE VIEW MultiTable
AS
    SELECT 'table1' AS TableName, * FROM table1
    UNION ALL
    SELECT 'table2' AS TableName, * FROM table2
    UNION ALL
    SELECT 'table3' AS TableName, * FROM table3

您的存储过程现在可以过滤表名:

Your stored procedure can now filter on the table name:

CREATE PROCEDURE test
    @TableName varchar(100)
AS
    SELECT * FROM MultiTable WHERE TableName = @TableName

这比使用动态 SQL 创建和执行更安全.

This is safer than using dynamic SQL creation and execution.

这篇关于在sql存储过程中传递表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！