Problem with dynamic query construciton

Posted on 2005-05-13
Last Modified: 2010-03-19
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.


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 =?
Question by:juststeve
    1 Comment
    LVL 28

    Accepted Solution

    To get the value from a dynamic query:

    DECLARE @vReturnValue INT

    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)) + '....

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now