none
Logical equivalent of Foreach in T-SQL RRS feed

  • Question

  • I am trying to accomplish the logical equivalent of the following in T-SQL:
    Foreach(row in T1)
    SELECT SecurityID FROM T2 WHERE row.EventID = T2.EventID

    I've Declared a table typed variable and I'd like to go through each row match data from table2 to the ID in table1 using SQL 2008.
    • Edited by P.Brian.Mackey Wednesday, August 5, 2009 3:45 PM version# added
    Wednesday, August 5, 2009 3:44 PM

Answers

  • Microsoft wrote an article that covers exactly what I want to do.  How to Iterate result sets in T-SQL: http://support.microsoft.com/default.aspx/kb/111401

    • Marked as answer by P.Brian.Mackey Wednesday, August 5, 2009 4:38 PM
    Wednesday, August 5, 2009 4:38 PM

All replies

  • If you only want to select the results, the following will produce the equivalent results.

    SELECT SecurityID FROM t2
    INNER JOIN t1 ON t1.EventID = t2.EventID
    Beyond Relational
    Wednesday, August 5, 2009 3:50 PM
    Moderator
  • Microsoft wrote an article that covers exactly what I want to do.  How to Iterate result sets in T-SQL: http://support.microsoft.com/default.aspx/kb/111401

    • Marked as answer by P.Brian.Mackey Wednesday, August 5, 2009 4:38 PM
    Wednesday, August 5, 2009 4:38 PM
  • Please note that, if your intention is only to select records, a LOOP may not be preferable. TSQL performs better with SET based queries rather than row-by-row processing.
    Beyond Relational
    Thursday, August 6, 2009 6:01 AM
    Moderator
  • Good point.  Ultimately I found INNER JOIN to be much more effective than iteration. 

    What do you get when put a C# programmer on T-SQL?  Iteration! :Þ
    おろ?
    Friday, August 14, 2009 2:01 PM
  • Heh.

    There is a difference between C# and SQL.

    For C#, let me reiterate: SQL is set in its ways.
    Friday, August 14, 2009 2:06 PM
    Moderator