CODEWITHSUNDEEP
sql-serverdynamic-sql
BacktoCoding9
BacktoCoding9

Reputation: 251

Accessing variable in SQL in a dynamic query

I am very new to SQL. I want to access a variable dynamically in a select statement.

declare @sql NVARCHAR(MAX)
declare @tableName varchar(100)
set @tableName='xxxx'

set @sql='select * from ' +@tableName+ 
EXEC sys.sp_executesql @sql

But every time I am executing the above query I am getting an error:

Incorrect syntax near the keyword 'EXEC'.

Upvotes: 0

Views: 267

Answers (3)

M.Ali
M.Ali

Reputation: 69574

declare @sql NVARCHAR(MAX);
declare @tableName NVARCHAR(128);
set @tableName='xxxx';

SET @sql = N'select * from ' + QUOTENAME(@tableName)
EXECUTE sp_executesql @sql

Use QUOTENAME() Function when concertinaing passed variables from users to your dynamic sql. It protects you against possible sql injection attack.

Upvotes: 1

RubberDuck
RubberDuck

Reputation: 12788

You had one too many plus(+) signs around @table name. Please note that this method is wide open to an injection attack though.

declare @sql NVARCHAR(MAX)
declare @tableName varchar(100)
set @tableName='xxxx'

set @sql='select * from ' +@tableName
EXEC sys.sp_executesql @sql

Upvotes: 0

D Stanley
D Stanley

Reputation: 152634

You've got an extra plus sign after @tableName - remove it:

set @sql='select * from ' +@tableName  /*+ */
EXEC sys.sp_executesql @sql

Upvotes: 0

Related Questions