I'm trying to execute some dynamic sql however I'm running into a problem with my variables .
set @SQL = 'Select @Variable = Field1 from ' + @DBNAME + '.dbo.Table'
error Msg 137 ... Must declare the variable @Variable.
Since the @variable is to be used to store the returned field value I can't simply do 'Select ' + @Variable + ' = Field1 from ' + @DBNAME + '.dbo.Table' or else it evaluates the @variable to nothing and the query ends up being ... 'Select = Field1 from DbName.dbo.Table' which of course fails.
How can I format this to work.