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.