How to use SQL Server table name in select query with variable?

Question

Is this wrong, is it possible to pass the table name to the select query dynamically?

This gives me the error "Must declare @TblName table variable"

DECLARE @TblName VARCHAR(30) SET @TblName = 'User' SELECT * FROM @TblName 

Answer 1

You need to create a dynamic SQL query, preferably using QUOTENAME . You can avoid any problems with malicious input by using the QUOTENAME function.

Here is an example script that illustrates how to query a table by creating a dynamic SQL query, passing the table name. You can change the table name by value to the @tablename variable.

Create and paste the script for the sample:

 CREATE TABLE sample ( id INT NOT NULL ); INSERT INTO sample (id) VALUES (1), (2), (3), (4), (5), (6); 

Dynamic SQL script:

 DECLARE @execquery AS NVARCHAR(MAX) DECLARE @tablename AS NVARCHAR(128) SET @tablename = 'sample' SET @execquery = N'SELECT * FROM ' + QUOTENAME(@tablename) EXECUTE sp_executesql @execquery 

Demo:

Click here to view the demo in SQL Fiddle.

Suggested reading:

Curse and blessings of dynamic SQL

Answer 2

you need to use dynamic sql execution

complete the statement in @selectstr

use exec sp_executesql @selectstr

Answer 3

You can do this using a dynamic query, check below

 DECLARE @TblName VARCHAR(30) DECLARE @vQuery NVARCHAR(100) SET @TblName = 'User' SET @vQuery = 'SELECT * FROM ' + @TblName EXECUTE sp_executesql @vQuery