Pranay Rana: Design TypeDataSet from Storedprocedure using temptable

Tuesday, July 17, 2012

Design TypeDataSet from Storedprocedure using temptable

I found one problem recently while designing TypeDataset in VisualStudio using storedproceudre 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

No comments:

Post a Comment