SQL Server Performance

Return Value from dynamic SQL Query

Discussion in 'General Developer Questions' started by vimalpercy, Jan 15, 2008.

  1. vimalpercy New Member

    Hello I need to obtain the value from dynamic SQL Query in a stored procedure
    SET @MyRetvalue=EXEC 'SELECT myretvalue from my table'
    how can i assign this value from the value i obtain from the execution of dynamic query?
    I know a way of creating a #TmpTable and inserting the value to that #tmpTable in the dynamic query and then pulling the value from that table and assigning to the variable.But i want to know if there is an efficient way to do that other than this?
    Thanks and Regards
  2. Adriaan New Member

    The proper way to execute dynamic SQL, especially if you need to get return values into variables, is to use sp_ExecuteSQL - check out the details in Books Online.
    It may look confusing at first, but it is the clearest way of doing this.
  3. FrankKalis Moderator

    In addition to Adriaan's response, see http://www.sommarskog.se/dynamic_sql.html for examples how to use sp_ExecuteSQL with an OUTPUT parameter.
  4. vimalpercy New Member

    Thanks Adriaan. I searched for that and found one useful link for sp_ExecuteSQLand i am putting it here so that somebody may use it when they are faced with similar problem.http://technet.microsoft.com/en-us/library/ms188001.aspxThanks and Regards
  5. padmajage123 New Member

    Thank you vimalpercy...

Share This Page