CODEWITHSUNDEEP
sqlsql-servert-sqlvariablesdynamic-sql
Jón Trausti
Jón Trausti

Reputation: 157

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

I'm currently using sp_executesql to execute a T-SQL statement with a dynamic table name. 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 rather like to have is a TABLE variable of which is a reference to a table, so I could do for example:

UPDATE TableRef SET ... WHERE ...

Because when I have really long T-SQL statements it gets really hard to read due to the format of it within a string.

Any suggestions would be helpful.

Upvotes: 9

Views: 9584

Answers (2)

codingbadger
codingbadger

Reputation: 43974

Why don't you pass the parameters to sp_executeSQL instead?

I'd also have a look at this article too.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

You can't. If you want to use a dynamic table name in your SQL, you have to concatenate it into your string.

If you have a lot of references to the table name within your query, you can shorten it by aliasing the table name, and for all other instances, use the alias.

e.g.

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

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

Upvotes: 0

Related Questions