有没有办法将表名指定为字符串? [英] Is there a way to specify table name as a string?

问题描述

假设我有一个这样的查询:

Let us say I have a query like this:

SELECT * FROM 
(
  SELECT * FROM 
  (
    SELECT * FROM DB.dbo.Table
  )
  INNER JOIN DB.dbo.Table ON ...

我通过手动更改各处的字符串，对不同的表多次运行此查询.我尝试声明以下内容:

I am running this query multiple times with different tables by manually changing the string everywhere. I tried declaring the following:

DECLARE @tablename AS VARCHAR(255)
SET @tablename = 'DB.dbo.Table'

但这似乎不起作用，因为它向我抛出一个错误，说我需要将 @tablename 声明为表变量，然后才能使用它.我如何模板化我的表名，如果可能，Intellisense 是否仍然有效?

But this does not seem to work as it throws me an error saying that I need to declare @tablename as a table variable before I can use it. How do I templatize my table name and if that is possible, will Intellisense still work?

推荐答案

您可以将其包装在 EXEC 语句中，如下所示:

You can wrap it in an EXEC statement like this:

declare @my_tablename nvarchar(100) = 'mytable';
exec('
SELECT * FROM 
(
  SELECT * FROM 
  (
    SELECT * FROM ' + @my_tablename + '
  )
  INNER JOIN ' + @my_tablename + ' ON ...'
);

但是不，智能感知在这种情况下不起作用.

But no, intellisense will not work in that scenario.

如果你事先知道你的输出会是什么样子，那么你可以声明一个临时表来保存结果，然后你可以在没有 EXEC 的情况下访问它.您将在临时表上拥有智能感知.

If you know what your output will look like in advance, then you can declare a temp table to hold the results, and then you can access that without EXEC. You will have intellisense on the temp table.

例如:

  --this must match whatever your SELECT is going to return
  CREATE TABLE #results(
    FIELD1 INT
   ,FIELD2 NVARCHAR(100)
   ,FIELD3 BIT
   );

EXEC('
  INSERT INTO #results(field1,field2,field3)
  SELECT FIELD1,FIELD2,FIELD3 FROM ' + @my_tablename
);

select * from #results  --you will have intellisense on #results

这篇关于有没有办法将表名指定为字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！