What is the best approach to code sub-procedure in T-SQL? I am thinking of using sp or function in one of my sp to behave like a sub-function where it would insert data to a temporary table for the main sp to use. Something like:
create proc spMain(....)
create table #temptable(....)
spSub() -- sp that populate the #temptable
select * from #temptable
I want to do this because depending on the input parameters, I would populate the temporary table with different data. Kindly let me know if this approach has any potential problems. Thanks.