Selecting from a # temporary table via a stored procedure into an XSD table adapter

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *