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'.
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.
You've got an extra plus sign after @tableName - remove it:
set @sql='select * from ' +@tableName /*+ */
EXEC sys.sp_executesql @sql
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