• Variable declared outside dynamic SQL aren't accessible within it, and vis versa

    Your first line of dynamic SQL reads 'SELECT top 1 @Rank = K.RANK...'

    Within the dynamic SQL, the variable @Rank has not been declared.

    If you're trying to get values out of dynamic sQL, then look up sp_executesql in books online and note the sections on declaring and passing parameters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass