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
-
Possible duplicate of Table name as variableunderscore_d– underscore_d2017-10-30 14:37:35 +00:00Commented Oct 30, 2017 at 14:37
Add a comment
|
3 Answers
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:
1 Comment
opperman.eric
For me, it only works without using Quotename
you have to use dynamic sql execution
wrap your statement in @selectstr
use exec sp_executesql @selectstr
3 Comments
Gratzy
the parameter for sp_executesql needs to be nvarchar/nchar/ntext
JNK
-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.
Turbot
very indeed answer @Sive provided , I would have learn that for next time :)
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
