Passing Database and Table names as Variables in SQL Server

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
Advertisements
This entry was posted in Programming and tagged , , , . Bookmark the permalink.

2 Responses to Passing Database and Table names as Variables in SQL Server

  1. William R Blair says:

    I have been trying to find to a simple way to pass Table Names to a Stored Procedure and have been struggling with the misery of Dynamic SQL Exec() and sp_executesql. This is both simple and elegant!
    Thank you so much!
    William R Blair

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s