How to use a variable on a table name?

Question

SET @tab = 'tableName'

SET @field1 = (SELECT field1 FROM '+@tab+' WHERE colName IS NULL)

I'm getting this error:

Must declare the table variable "@tab".

I need to set the result on @field1

Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) — Jacob H, Mar 12 '18 at 20:07
You have to use dynamic SQL in any database. The specific syntax varies by database. — Gordon Linoff, Mar 12 '18 at 20:21
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please dd the tag for the database product you are using `postgresql`, `oracle`, `db2`, `sql-server`, ... — a_horse_with_no_name, Mar 12 '18 at 21:57
Tag properly!!!! Which dbms is this???? It's useless if someone gives you a solution to the wrong dbms. — Eric, Mar 12 '18 at 22:12
Thanks for all help. I'm using SQL Server. This is a small part of a transact sql. — Sergio Paiva, Mar 13 '18 at 00:05

Daryl Bagley · Answer 1 · 2018-03-13T15:15:33.593

0

For a SQL Server database, use EXECUTE sp_executesql to run the dynamic sql you setup. (Notice the correct placement of the quotes compared to your post).

SET @tab = 'tableName'
SET @field1 = 'SELECT field1 FROM ' + @tab + ' WHERE colName IS NULL'
EXECUTE sp_executesql @field1

However, to save the return value you should look into defining OUTPUT as shown in this answer: https://stackoverflow.com/a/3840771/9392034

edited Mar 13 '18 at 15:15

answered Mar 12 '18 at 22:06

Daryl Bagley

17
6

Sorry guys. I'm using SQL Server. This is a small part of a transact sql. – Sergio Paiva Mar 13 '18 at 00:00
@SergioPaiva - I've updated the answer to indicate that it is for SQL Server. – Daryl Bagley Mar 13 '18 at 15:16

How to use a variable on a table name?

1 Answers1