CTE idea unfortunately didn't work (as I expected). I guess I may try temporary table instead. Can anyone give me PROS & CONS for using temp table vs. table-valued function?

>Hi everybody,
>
>I have a stored procedure called PersonSelect. It selects information from several tables depending on person type. For all person types 3 tables and fields from them are the same, the rest of tables varies depending on person type. Currently I repeat long select statement for each person type adding extra fields and JOINS as needed.
>
>However, I think I need to re-organize this SP and put "common" part into some separate storage. I'm just not sure what should I try here. Select ... INTO table #tmpPerson ... ?
>
>Or may be I can use a table function here?
>
>Or is there any other way, say, using CTE? http://www.4guysfromrolla.com/webtech/071906-1.shtml I'm afraid I would not be able to add conditional statement before attempting to select from it, but I guess I need to try it.
>
>What do you think my best option should be?
>
>Thanks a lot in advance.