How do I use SQL Server table name in select query with a variable?

sql-server, sql-server-2005, sql-server-2008, t-sql

Solution

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

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

Create and insert script for 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 read:

The Curse and Blessings of Dynamic SQL

Problem

Is this incorrect, can't we pass the table name to a select query dynamically? This is giving me a error 'Must declare the table variable @TblName' ``` DECLARE @TblName VARCHAR(30) SET @TblName = 'User' SELECT * FROM @TblName ```

Original source

Related problems