Hi, I am calling one sp say spABC from another sp say spXYZ. spABC returns 3 tables. How can i identify 3 distinct tbles in my calling sp? I need to set values in all tables depending on the value in one column of one table.
what do you mean when you say, "spABC returns 3 tables"? If you mean 3 seperate result sets, you won't be able to capture them in a calling SP unless all result sets have the same columns returned. If they do you could:
insert #temp(<colList>) exec myCalledSP
But again, the SP can't return one or more Sql Server Table objects. They can only return result sets, output variables (not of table type), and return codes.