SQL_Surfer (7/28/2013)
Sorry, I cannot post the actual code for security reasons....
Then obfuscate column and table names! Your pseudocode is so full of errors that making sense of it is more effort than writing the query.
Here's a best guess based upon the random ramblings you've posted so far:
SELECT Result = ReturnVal1 + ReturnVal2 + ReturnVal3 + ReturnVal4 + ReturnVal5
FROM Mytable m
OUTER APPLY (
SELECT ReturnVal5 = RetVal, Val4 FROM Table5 WHERE Val5 = m.mycol
) t5
OUTER APPLY (
SELECT ReturnVal4 = RetVal, Val3 FROM Table4 WHERE Val4 = ISNULL(t5.Val4,m.mycol)
) t4
OUTER APPLY (
SELECT ReturnVal3 = RetVal, Val2 FROM Table3 WHERE Val3 = ISNULL(t4.Val3,m.mycol)
) t3
OUTER APPLY (
SELECT ReturnVal2 = RetVal, Val1 FROM Table2 WHERE Val2 = ISNULL(t3.Val2,m.mycol)
) t2
OUTER APPLY (
SELECT ReturnVal1 = RetVal FROM Table1 WHERE Val1 = ISNULL(t2.Val1,m.mycol)
) t1
WHERE m.mycol = @a
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden