When making complex stored procedures it is sometimes necessary to aggregate your results into a temporary table before you do your final select. This approach will work fine until you try to add this stored procedure to an XSD table adapter. Doing this will normally result in the following error message
Generated SELECT statement. Invalid object name ‘#temptable’
This error makes sense as visual studio will not be able to get a definition for the table to hard-wire up the table adapter.
However, if you add the following code to the very top of your stored procedure it will work.
SET FMTONLY OFF
I have no idea why this work but it does. If you know why it works I would be very interested to know why in the comments below.