--CREATE PROCEDURE addWeight
declare
   @weight INT,
   @name VARCHAR(40),
   @column VARCHAR(40)
--AS
select @weight=0, @name='myTable', @column='myColumn'

declare @sql nvarchar(max)=N'select * from (VALUES (@weight, (SELECT TOP 1 @column FROM @name ORDER BY @column DESC)))v(a,b);'
declare @run nvarchar(max)=replace(replace(replace(@sql,'@weight', @weight),'@column',@column),'@name',@name)

exec(@run)
-- OK

exec sp_executesql 
   @sql, N'
   @weight INT,
   @name VARCHAR(40),
   @column VARCHAR(40)',
   @weight=@weight, @name=@name, @column=@column
-- Error 1087: Must declare the table variable "@name".