Must declare the scalar variable

Question

what is wrong with the following query ??

declare  @dbName varchar (50)
declare  @fldName varchar (50)

set  @dbName ='student';
set  @fldName='stID';

declare  @gID smallint
set  @gID =4;


DEClARE @sqlstr nvarchar(max);

SET @sqlstr = 'SELECT  MAX([' + @fldName +'])
  FROM  [dbo].['+ @dbName +'] 
  WHERE [dbo].['+ @dbName +'].[gID ] = @gID';

  EXEC (@sqlstr)

Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable

Answer 1

Execution of the variable @sqlStr is done under a separate context than that in which you are currently running. Therefore the variables that you have declared in your session will not be available.

Try this instead:

declare @params = N'@gID smallint';
exec sp_executesql @sqlStr, @params, @gID = @gID;

Documentation for sp_executesql can be found here:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Full code example:

declare @dbName varchar (50) = 'student';
declare @fldName varchar (50) = 'stID';
declare @gID smallint = 4;
declare @params nvarchar(max) = N'@gID smallint';
declare @sqlstr nvarchar(max) = 
        N'select max([' + @fldName +'])
            from [dbo].['+ @dbName +'] 
           where [dbo].['+ @dbName +'].[gID ] = @gID';
exec sp_executesql @sqlStr, @params, @gID = @gID; 

Answer 2

as brownstone suggested,you have to use sp_executesql,adding one more input .GO operator also has the same behaviour.

Example:

declare @f int
set @f =3

go

select * from test where col1=@f

Above example throws an error,since GO operator is a batch seperator.In Summary,table variable scope is limited to session or batch they are executed