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?
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
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.
You use dynamic SQL. Not sure why you need so many nested SELECTs but it would be something like:
DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ' + @tablename + '...';
EXEC sp_executeSQL @sql;
However please be aware of SQL injection. And no, IntelliSense has no ability to parse a string for object names (or to even know while editing what object name is going to be there).
