Problem with dynamic query construciton

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 =?
juststeveAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
To get the value from a dynamic query:

DECLARE @vReturnValue INT
DECLARE @strSQL NVARCHAR(4000)

SET @strSQL = N'SELECT TOP 1 @vReturnValue = Unit FROM ' + @varTable + ' WHERE myIdentity = ' + @myIdentity + ')'
EXECUTE sp_executesql @strSQL, N'@vReturnValue INT OUT', @vReturnValue OUT

SET @mySQL = @mySQL + ' VALUES (' CAST(@vReturnValue AS VARCHAR(10)) + '....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.