Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: proc execution plans, dynamic sql and a new one on me

    new one on me. and i must be total ignorant hack or there is something else going on here. Someone just showed me a proc with some dynamic sql in it. The dynamic sql extracted from the proc by doing a print @SQL produces a exec plan different than anything in the stored procedure itself. They say it is because it is because dynamic sql is in a different @SPID, but then I threw this at them...

    Code:
    ALTER PROC testSpid
    As
    DECLARE @SQL nvarchar(50)
    
    SET @SQL = 'SELECT @@SPID'
    
    SELECT @@SPID
    
    --EXEC(@SQL)
    exec sp_executesql @SQL
    
    GO
    
    EXEC testSpid
    All they said was "Hmph". Their solution relies on the problem they can not see inexecution plan of the proc. What's doing?
    Last edited by Thrasymachus; 12-23-09 at 13:20.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Not sure I understand the problem. Are you saying that a particular SQL statement run by sp_executesql creates a different plan that the same SQL statement run alone in a batch?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •