动态表名的查询SQL

CREATE TABLE [dbo].[TB_User](
 [ID] [int] NOT NULL,
 [Name] [varchar](50) NOT NULL,
 [Score] [varchar](50) NULL,
 ) ON [PRIMARY]

INSERT INTO [dbo].[TB_User]
   ([ID],[Name] ,[Score])
 VALUES (1,'Lucy','');
INSERT INTO [dbo].[TB_User]
   ([ID],[Name] ,[Score])
 VALUES (1,'Lili','');
INSERT INTO [dbo].[TB_User]
   ([ID],[Name] ,[Score])
 VALUES (1,'Jack','');

declare @exesql nvarchar(1000);
declare @table varchar(50);
set @table='TB_User';
set @exesql='SELECT *  FROM [dbo].'+@table;
execute sp_executesql @exesql

declare @exesql nvarchar(1000);
declare @table varchar(50);
set @table='TB_User';
set @exesql='update [dbo].'+@table+' set Score=''Good''';
execute sp_executesql @exesql

过程 sp_executesql，参数类型必须为 'ntext/nchar/nvarchar'

另外的例子：

 

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT * FROM AdventureWorks.HumanResources.Employee
     WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
--------------------------------------------------------------------

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title)
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

-----------------------------------------------------------
更多的解释请参考 微软帮助文档。