• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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 =?
0
juststeve
Asked:
juststeve
1 Solution
 
rafranciscoCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now