Ah, found the issue. A little further investigation pointed to adding 'OUTPUT' to the @ThisValue = @ThisValue, so the exec now becomes
exec sp_executesql @ThisSQL, N'@ThisValue nvarchar(30) OUTPUT', @ThisValue = @ThisValue output

And that does what I need it to do. Thanks again for pointing out the correct path.



>Hi Borislav
>
>I see where we're going with this, and now, that section looks like
>

>  declare @TheOutputValue nvarchar(30) = ''
>  set @ThisSQL =  '(Select @TheOutputValue = CAST(' +  @ThisField  + ' as nvarchar(30)) from ' 
>      + @FileName + ' where ' + @KeyFieldName + ' = ' + cast(@KeyFieldValue as Varchar) + ')'
>  exec sp_executesql @ThisSQL, N'@ThisValue  nvarchar(30) OUTPUT', @ThisValue = @ThisValue 
>

>
>It keeps telling me I need to declare the scalar variable "@TheOutputValue", so (obviously) there's still something I'm missing
>
>
>>
>>
>>

>> exec sp_executesql @ThisSQL, @ThisValue out    -- This works and shows result in 'results' tab
>>

>>The second parameter of this SP is declaration of parameters for the passed dynamic sql.
>>So if you want to get value from this Dynamic SQL the you must use output parameter that can get value:
>>
>>

>>...
>> set @ThisSQL = '(Select @TheOutptValue = CAST(' +  @ThisField  + ' as nvarchar(30)) from ' 
>>      + @FileName + ' where ' + @KeyFieldName + ' = ' + cast(@KeyFieldValue as Varchar) + ')'
>>
>>exec sp_executesql @ThisSQL, N'@ThisValue  nvarchar(30) OUTPUT', @ThisValue = @ThisValue 
>>
>>print TheOutptValue
>>...
>>