如何在 SQL Server 查询的 FROM 子句中使用变量?

2021-09-10 00:00:00 tsql sql-server

我正在创建一个查询，该查询将选择表中的所有数据.查询将根据我将传递给存储过程的变量选择到表中.

I am creating a query that will select all data on the table. The query will select to the table base on the variable that I will pass on the stored procedure.

在我的例子中.如果我执行 example_sp table1，它将在 table1 中选择.同样的事情，如果我使用 example_table table2，它应该选择 table2.

Here in my example. If I execute example_sp table1 it will select in table1. Same thing if I use example_table table2, it should selecttable2.

ALTER PROCEDURE example_sp
    @type varchar(10), -- value will be `table1` or `table2`
AS
BEGIN
    SELECT * FROM @type
END

推荐答案

Gordon 建议的版本略有不同......

A slightly different version of what Gordon has suggested.....

ALTER PROCEDURE example_sp
    @TableName SYSNAME   --<-- Use appropriate data type for sql server objects
AS
BEGIN
  SET NOCOUNT ON;

  Declare @Sql NVARCHAR(MAX);

  SET @Sql = N' SELECT * FROM ' + QUOTENAME(@TableName)

  Exec sp_executesql @Sql
END

相关文章