Use:

CREATE PROCEDURE [dbo].[GetByName]
    @TableName NVARCHAR(100)
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sSQL nvarchar(500);

    SELECT @sSQL = N'SELECT * FROM' + QUOTENAME(@TableName);

    EXEC sp_executesql @sSQL
END

变量不能使用表名。你必须这样做:

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * from yourtable'
EXEC (@sqlCommand)

对于静态查询，比如您问题中的查询，表名和列名需要是静态的。

对于动态查询，您应该动态生成完整的SQL，并使用sp_executesql来执行它。

下面是一个脚本示例，用于比较不同数据库的相同表之间的数据:

静态查询:

SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]

因为我想轻松地更改表和模式的名称，所以我创建了这个动态查询:

declare @schema sysname;
declare @table sysname;
declare @query nvarchar(max);

set @schema = 'dbo'
set @table = 'ACTY'

set @query = '
SELECT * FROM [DB_ONE].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
EXCEPT
SELECT * FROM [DB_TWO].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);

EXEC sp_executesql @query

由于动态查询有许多需要考虑的细节，而且它们很难维护，我建议您阅读:动态SQL的诅咒和祝福

Use:

CREATE PROCEDURE [dbo].[GetByName]
    @TableName NVARCHAR(100)
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sSQL nvarchar(500);

    SELECT @sSQL = N'SELECT * FROM' + QUOTENAME(@TableName);

    EXEC sp_executesql @sSQL
END

使用sp_executesql来执行任何SQL，例如:

DECLARE @tbl    sysname,
        @sql    nvarchar(4000),
        @params nvarchar(4000),
        @count  int

DECLARE tblcur CURSOR STATIC LOCAL FOR
   SELECT object_name(id) FROM syscolumns WHERE name = 'LastUpdated'
   ORDER  BY 1
OPEN tblcur

WHILE 1 = 1
BEGIN
   FETCH tblcur INTO @tbl
   IF @@fetch_status <> 0
      BREAK

   SELECT @sql =
   N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
   N' WHERE LastUpdated BETWEEN @fromdate AND ' +
   N'                           coalesce(@todate, ''99991231'')'
   SELECT @params = N'@fromdate datetime, ' +
                    N'@todate   datetime = NULL, ' +
                    N'@cnt      int      OUTPUT'
   EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

   PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END

DEALLOCATE tblcur

您需要使用SQL Server动态SQL:

DECLARE @table     NVARCHAR(128),
        @sql       NVARCHAR(MAX);

SET @table = N'tableName';

SET @sql = N'SELECT * FROM ' + @table;

使用EXEC执行任何SQL:

EXEC (@sql)

使用EXEC sp_executesql执行任何SQL:

EXEC sp_executesql @sql;

使用EXECUTE sp_executesql执行任意SQL:

EXECUTE sp_executesql @sql