In SQL Server, how to create a reference variable in a table?

Question

I am currently using sp_executesql to execute a T-SQL statement with the name of a dynamic table. However, it is really ugly to see something like:

set @sql = 'UPDATE '+Table_Name+' SET ... WHERE '+someVar+' = ... AND '+someVar2' = ...' sp_executesql @sql 

What I would like to have is a TABLE variable, which is a table reference, so I could do, for example:

 UPDATE TableRef SET ... WHERE ... 

Because when I have very long T-SQL statements, it is very difficult to read because of its format inside the string.

Any suggestions would be helpful.

Answer 1

Why don't you pass sp_executeSQL parameters instead?

http://msdn.microsoft.com/en-us/library/ms188001.aspx

I would also read this article http://www.sommarskog.se/dynamic_sql.html

Answer 2

You can not. If you want to use the dynamic table name in your SQL, you need to associate it with your row.

If you have many references to the table name in your query, you can shorten it by superimposing the table name, and for all other instances, an alias.

eg.

 SET @SQL = 'UPDATE t SET.... FROM ' + @TableName + ' t WHERE ....' 

Just be very careful when using dynamic SQL like this. Make sure you protect yourself from SQL injection.