This proc demonstrates

1. left Outer joins in SQLServer
2. left join by default is a left outer join
3. You can use a sub-select as a column value
4. You can use top 1 to picke the top row for case 3
5. You can use a function to get the value for a column
6. For 4 and 5, you can use the values of other columns as inputs
7. Use of inner selects for outer joining multiple tables
8. Demonstrates how to pass input arguments and use them