You have 2 variables named @res. One inside the dynamic code, and one outside. The one inside the dynamic code is lost and the one outside is printed but never gets a value assigned. You need to use parameters on your dynamic code if you want to do something other than print the value. Or you could include the print statements in your dynamic code.

Declare @s-2 nvarchar(max);

Declare @i int, @max-2 int

Declare @tab varchar(1000);

Declare @res VARCHAR(100);

Select @max-2= COUNT(*) FROM #t;

Set @i=1;

While( @i <= @max-2 )

Begin

Select @tab=name from #t where ID = @i;

Select @s-2 ='Select @res=MAX(SourceID) FROM ' + @tab

/* Need Help */

Exec sp_executesql @s-2, N'@res VARCHAR(100) OUTPUT', @res OUTPUT;

PRINT @res

Select @i=@i+1;

End