>I'm trying to get a list of values based off a series of sql.
>
>What is going on is
>
>passing in a list of field names for a particular table, parsing that list and then getting the value of those fieldnames to save off
>
>

>declare
>  @KeyFieldValue int = 123456, 
>  @FileName varchar(25) = 'SuperVisionLevel',
>  @KeyFieldName varchar(10) = 'LevelKey',
>  @FileList varchar(300) = 'LevelKey,CountyOffenderKey,SupvLevel,BeginDate,AuditDate,',
>  @Name varchar(50),
>  @SID varchar(30),
>  @PID varchar(30)
>
>
>declare @cLoc int, @strlen int, @ThisField varchar(25),
>        @ColValues varchar(250), @ColCnt int, @ThisValue nvarchar(30),
>        @ThisSQL nvarchar(300), @HoldList varchar(300)
>
>set @ColValues = ''
>set @ColCnt = 1
>set @strlen = len(@FileList)
>set @HoldList = @FileList
>while @strlen > 0
>begin
>  set @strlen = len(@FileList)
>  set @cloc = charindex(',', @FileList)
>  set @ThisField = substring(@FileList, 1, @cloc-1)
>  set @FileList = substring(@FileList, @cloc+1, @strlen - (@cloc))
>  print @FileList
>  set @ThisSQL = '(Select ' +  @ThisField  + ' from ' 
>      + @FileName + ' where ' + @KeyFieldName + ' = ' + cast(@KeyFieldValue as Varchar) + ')'
>  exec sp_executesql @ThisSQL, @ThisValue out    -- This works and shows result in 'results' tab
>print @ThisSQL                                                          -- Nothing prints here
>  set @ColValues = @ColValues + ';' + @ThisValue     -- This...not so much, and when
>  set @strlen = len(@FileList)
>end
>

>
>Can this work? If so, what am I 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
...