Using "Multiple Assignment Variables" for a Generic Compare Data Script

• Paul White

  SSC Guru

  Points: 150468

  More actions

  January 27, 2010 at 8:18 pm

  #1109704

  scott mcnitt (1/27/2010)

  ---

  MAV query is a very useful techique. I am sure it is something I am doing wrong (or is there a database setting I am missing) but I only get one column in my output when I should get 117.

  That's another problem with this technique: it is possible for a plan to be produced where the desired 'concatenation' doesn't work, and just one of the candidate values ends up in the variable. The query you posted happened to work 'correctly' on the instance I tested it with - but it is not guaranteed.

  Also, note that the ORDER BY clause in your query only applies to the final output of rows - it does not enforce an order for the multiple assignment operation.

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi