7

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
Pinu
  • 7,310
  • 16
  • 53
  • 77

3 Answers3

14

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

1

you have to use dynamic sql execution

wrap your statement in @selectstr

use exec sp_executesql @selectstr

Turbot
  • 5,095
  • 1
  • 22
  • 30
  • 1
    the parameter for sp_executesql needs to be nvarchar/nchar/ntext – Gratzy May 09 '12 at 17:45
  • 3
    -1 - It's really a bad idea to just give someone code for dynamic SQL without explaining it at all. `Here's a gun, try not to shoot yourself.` – JNK May 09 '12 at 18:00
  • very indeed answer @Sive provided , I would have learn that for next time :) – Turbot May 10 '12 at 13:07
0

You can do this thing by using dynamic query, Check below

DECLARE @TblName VARCHAR(30)

DECLARE @vQuery NVARCHAR(100)

SET @TblName = 'User'

SET @vQuery = 'SELECT * FROM ' + @TblName

EXECUTE sp_executesql @vQuery
Asif
  • 2,657
  • 19
  • 25