Design TypeDataSet from Storedprocedure using temptable

I found one problem recently while designing TypeDataset in VisualStudio using storedprocedure which is making use of temporary table to get result.
 
Here is detail of the it what I did and how I resolved the issue. 

 
Step 1: Created Procedure with Temporary table

create PROCEDURE [dbo].[GetData]
AS
begin
   create TABLE #MyTable  (
  ID int,
  Name nvarchar(50) )
 
 INSERT INTO #MyTable (ID, Name)
 SELECT  PersonID, FirstName + ' ' + LastName
 FROM  dbo.Person
 
 SELECT ID,
  Name 
 FROM #MyTable
end

 

Step 2: Add TableAdapter in the design view of TypeDataSet and create database connection
 

Step 3: Create Procedure or Select Existing procedure
 

Step 4 : Select Procedure that I created already

Note : here it's not displaying DataColumn of select statement related to proceudre

Step 5 : Click on finish it display that in valid object #table

 
so it doesn't able to create tableadapter for procedure and display like this

 
Solution

To resolve this issue you can try one of the following solution , I tried first solution because its easy and not require to change in my existing storedprocedure code
 

Solution 1

Just add below line at top of the procedure after begin statement

SET FMTONLY OFF

This will resolve the issue and allow to create tableadapter easily without any error. So procedure will be

create PROCEDURE [dbo].[GetData]
AS
begin
  SET FMTONLY OFF
   //code of the procedure as above
end

Solution 2

To try this solution just create table variable instead of temporary table in procedure. So procedure will be

create PROCEDURE [dbo].[GetData]
AS
begin
  DECLARE @MyTable TABLE (
  ID int,
  Name nvarchar(50) )
 
 INSERT INTO @MyTable (ID, Name)
 SELECT  PersonID, FirstName + ' ' + LastName
 FROM  dbo.Person
 
 SELECT ID,
  Name 
 FROM @MyTable
end

 
After trying the above solution tableadapter on XSD file will be like this