Is there a way to specify table name as a string?

Question

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'

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?

Answer 1

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.

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.

For example:

  --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

Answer 2

No. Just as you cannot specify the function name in your c# program as a string. T-SQL compilation is supposed to come up with a precise access plan, meaning what indexes to open and use to satisfy the query. It would be impossible to come up with a plan for a 'string', just as it would be impossible in C# to generate the code to invoke a 'string' as a method.

The solution is dynamic SQL:

declare @sql NVARCHAR(MAX) = N'SELECT ... FROM ' +
   quotename(@dbname) + N'.' + quotename(@schema) + N'.' + quotename(@table) + 
   N' WHERE ...';
exec sp_executesql @sql;

... just as in C# you would use reflection to do dynamic runtime invocation.

For more info see The Curse and Blessings of Dynamic SQL.

PS. the split of @tablename into components and the use of QUOTENAME is an absolute must, it guards agaisnt SQL Injection. Use PARSENAME to do the split for you.