I often want to apply the same SQL to one or more tables in one or more databases. This requires passing the database and table as a variable (or copying and pasting the code multiple times).
Below are three ways to pass a database table name as a variable.
Version 1 is a very common option, building a string then processing it with EXEC. This is okay, but I do find it very untidy, and difficult to read an debug.
Version 2 is a slightly better option, using tokens in the SQL string and then replacing before processing with EXEC. Although this is a bit tidier I still find it difficult to read and debug.
Version 3, which I recently came up with in one of those “there has to be a better way” moments. This uses the SYNONYM (only available in SQL 2005 and above). The nice thing about this is that once you’ve created your SYNONYM all the following code gets coloured and highlighted in the normal way, and in my opinion makes for much easier reading.
I haven’t found there to be any obvious performance differences, however, I’ve only just tried this out. I would expect that Version 3 may be quicker than the other two dynamic methods, but that may just be wishful thinking.
--Version 1 DECLARE @tbl VARCHAR (500) DECLARE @sql VARCHAR (500) SET @tbl = 'cofely_data.bags.SAC_LOG' SET @SQL = 'SELECT TOP 100 * FROM ' + @tbl EXEC (@SQL) --Version 2 DECLARE @tbl VARCHAR (500) DECLARE @sql VARCHAR (500) SET @tbl = 'cofely_data.bags.SAC_LOG' SET @SQL = 'SELECT TOP 100 * FROM ~DB~' SET @SQL = REPLACE(@SQL, '~DB~' , @tbl ) EXEC (@SQL) --Version 3 DECLARE @tbl VARCHAR (500) DECLARE @sql VARCHAR (500) SET @tbl = 'cofely_data.bags.SAC_LOG' SET @sql = 'CREATE SYNONYM myTable FOR ' + @tbl EXECUTE (@sql) SELECT TOP 100 * FROM myTable DROP SYNONYM myTable