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

Question

Say I have a query like this:

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

I execute this query several times with different tables, manually modifying the entire row. 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 an error saying that I need to declare @tablename as a table variable before I can use it. How can I templatize the name of my table, and if possible, will Intellisense work?

Answer 1

You can wrap it in an EXEC statement as follows:

 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 this scenario.

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

For instance:

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

Not. Just like you cannot specify a function name in your C # program as a string. T-SQL compilation is supposed to contain an exact access plan, which means which indexes to open and use to satisfy the query. It would be impossible to plan for a "string", as it would be impossible in C # to generate code to call "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 like in C #, you should use reflection to make a dynamic runtime call.

See Curse and blessings of dynamic SQL for more information .

PS. splitting @tablename into components and using QUOTENAME is an absolute must; it protects agaisnt SQL Injection. Use PARSENAME to split for you.

Answer 3

You are using dynamic SQL. You don’t know why you need so many nested SELECTs, but it will be something like:

 DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ' + @tablename + '...'; EXEC sp_executeSQL @sql; 

However, keep in mind that SQL injection. And no, IntelliSense does not have the ability to parse a string for object names (or even know when editing which object name will be there).