I would like to assign a select value to a variable using sql string. Below is my code:
DECLARE @maxRow INT, @tableName NVARCHAR(128) = N'whatever';
select @maxRow=Max(id) from @tableName
But this throws an error:
Msg 1087, Level 16, State 1, Line 3
Must declare the table variable "@tableName".
Even though I did declare the variable.
You can't use a variable for a table name unless you put it into dynamic SQL. The code is currently expecting @tableName to be a table variable, not a string, though it's clear from your syntax this is not what you intended. This usage smells like sub-optimal design and lends itself to significant SQL injection risks, but you can try this code instead:
DECLARE @maxRow INT;
DECLARE @sql NVARCHAR(MAX) = N'SELECT @maxRow = MAX(id) FROM '
+ QUOTENAME(@tableName) + ';';
EXEC sp_executesql @sql, N'@maxRow INT OUTPUT', @maxRow OUTPUT;
PRINT @maxRow;
Please do read up on SQL injection and normalization.