1

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

  • WHERE [dbo].['+ @dbName +'].[gID ] = ' + CAST(@gID AS VARCHAR(5)); – Mark Sinkinson Dec 7 '15 at 11:16
2

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; 
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.