CODEWITHSUNDEEP
sqlsql-server
Peter Hui
Peter Hui

Reputation: 119

SQL SERVER using a variable that contains a table

I want to use a table as a variable but it gives me an error.

   DECLARE @tbl_var as varchar(50)
   SET @tbl_var = 'Table_Sales'
   SELECT * FROM @tbl_var

I understand that I need to declare the variable @tbl_var as a table, but even when I do it gives me an error

    DECLARE @tbl_var as table
   SET @tbl_var = 'Table_Sales'
   SELECT * FROM @tbl_var

Also, it's not possible for me to declare all the columns because it has a hundred columns.

   DECLARE @tbl_var as table ( A as varchar(10), B as varchar(10)....)
       SET @tbl_var = 'Table_Sales'
       SELECT * FROM @tbl_var

Any help is appreciated

Upvotes: 0

Views: 47

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Try to google and read about "Dynamic SQL"

DECLARE @tbl_var as varchar(50)
SET @tbl_var = 'Table_Sales'

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM ' + @tbl_var

EXEC (@sql);

OR (better):

EXEC sp_executesql @sql;

Upvotes: 3

Related Questions