I'm constructing an insert statement via a dynamic query string because it depends on a variable for the 'FROM' clause:
@mySQL = ' insert ' + @varTable + ' (Unit, ...'
That much I can handle. Execpt that one of the values to be inserted has to be obtained by a seperate dynamic query because, like my main query, it depends on a variable for the 'FROM' clause.
@mySQL = @mySQL + ' VALUES ( ' + @SCALAR_VALUE_FROM_SUBQUERY + '....
So I'd try something like this:
set @strSQL = 'set @myUnit = (select top 1 Unit from ' + @varTable + ' where myIdentity = ' + @myIdentity + ')'
set @myUnit = exec(@strSQL)
and then construct my primary query. Except for the complier error i get....seems i can't call the exec on the right side of the =?