SQL server 2012 - return variable value from a dynamic script -

i'm trying run dynamic script return variable can pass in rest of script. i've couple of ways of google, think still haven't got syntax correct, therefore getting error or null value returned.

can please advise i've gone wrong.

for example: return value variable @table_name asia database , set variable appended table name retrieved table , t5148 id table turn table name variable. have set variables script sits when other scripts loops

thank you

declare @table_name nvarchar(50) declare @database nvarchar(50) declare @id nvarchar(50) declare @sql nvarchar(max)  set @database = 'asia' set @id = 't5178'  set @sql = n'select @table_name = ''@database''+table_name      ''@database''+tables (nolock)     id = ''@id'''  exec sp_executesql @sql, n'@table_name nvarchar(50) output', @table_name   output  select @tran_table 

if not wrong, need :

declare @table_name nvarchar(50) declare @database nvarchar(50) declare @id nvarchar(50) declare @sql nvarchar(max)  set @database = 'asia' set @id = 't5178'  set @sql = n'select @table_name = table_name      ' + @database+'.dbo.tables (nolock)     id = @id'  exec sp_executesql  @sql, n'@id nvarchar(50),@table_name nvarchar(50) output',                      @id = @id, @table_name = @table_name output  set @table_name = @database + @table_name 

note : change dbo schema name.