none
How to execute Stored Procedure and get return data when temporary table is used , using Entity Framework? RRS feed

  • Question

  • I have a stored procedue something like this :

    ALTER PROC [dbo].[Proc1] ( @Param1 INT, @Param2 INT ) AS Begin IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL DROP TABLE #TEMP1 IF OBJECT_ID('TEMPDB..#TEMP2') IS NOT NULL DROP TABLE #TEMP2 SELECT Col1,Col2 INTO #TEMP1 FROM Table1 WHERE Col1 = @Param1 AND Col2 = @Param2 SELECT Col3,Col4 INTO #TEMP2 FROM Table2 WHERE Col1 = @Param1 AND Col2 = @Param2 SELECT T1.Col1,T1.Col2 FROM #TEMP1 T1 LEFT OUTER JOIN #TEMP2 T2 ON T1.Col1 = T2.Col1 GROUP BY .... and query goes like thid

    END

    Using Entity Framework i added this SP in my object model. Now when i try to add function import i pressed "Get Column Information" button to get columns returned by SP, but it says "The selected Stored procedure returns no column".
    Earlier i was using SP without temporary tables but i was successfully able to add Function import with all return data.

    But this time i am getting problem, i am not able to get return columns. Can anyone help me.

    Thanks.

    Saturday, December 22, 2012 10:42 AM

Answers

  • Hi,

    this link may help : http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/e7f598a2-6827-4b27-a09d-aefe733b48e6


    One good question is equivalent to ten best answers.

    • Marked as answer by NorthValley Sunday, December 23, 2012 8:57 AM
    • Unmarked as answer by NorthValley Sunday, December 23, 2012 3:28 PM
    • Marked as answer by NorthValley Monday, December 24, 2012 9:45 AM
    Sunday, December 23, 2012 8:42 AM

All replies

  • Hi,

    this link may help : http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/e7f598a2-6827-4b27-a09d-aefe733b48e6


    One good question is equivalent to ten best answers.

    • Marked as answer by NorthValley Sunday, December 23, 2012 8:57 AM
    • Unmarked as answer by NorthValley Sunday, December 23, 2012 3:28 PM
    • Marked as answer by NorthValley Monday, December 24, 2012 9:45 AM
    Sunday, December 23, 2012 8:42 AM
  • Is there any specific and well thought out reason why you use temporary tables? 

    Do try table variables instead!


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Sunday, December 23, 2012 10:53 AM
  • Is there any specific and well thought out reason why you use temporary tables? 

    Do try table variables instead!


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Yes, as given in my first post i am querying data from two tables storing them in temporary tables and then applying joins on them. Finally i am using Select statememt to give output, but through Entity Framework when adding Function import to added stored procedure, when i press Get Column information it says procedure returns no column.

    When i added "SET FMTONLY OFF" i got column information of a few of Stored procedure when adding function import but most of the remaining SP's column information i did'nt get although i have used "SET FMTONLY OFF" in all SP. And there is no any difference between any procedure, all  SP are relatively same,only tables are changing.

    So why for some SP it is working and for some other it is not working ?

    Can anyone please help me !


    • Edited by NorthValley Sunday, December 23, 2012 3:43 PM
    Sunday, December 23, 2012 3:34 PM
  • Because of the temporary tables. 

    Use table variables instead!

    ALTER PROC [dbo].[Proc1] 
    (
    @Param1 INT,
    @Param2 INT
    )
    AS
    Begin
    Declare @Temp1 TABLE (Col1 int, Col2 varchar(150));
    INSERT INTO @Temp1 (Col1,Col2)
    SELECT Col1,Col2
    FROM Table1
    WHERE Col1 = @Param1
    AND Col2 = @Param2

    Declare @Temp2 TABLE (Col3 int, Col4 decimal(12,4));
    INSERT INTO @Temp1 (Col3,Col4)
    SELECT Col3,Col4
    INTO #TEMP2
    FROM Table2
    WHERE Col1 = @Param1
    AND Col2 = @Param2

    SELECT T1.Col1,T1.Col2
    FROM @Temp1 as T1 LEFT OUTER JOIN
    @Temp2 as T2 
    ON T1.Col1 = T2.Col1
    GROUP BY .... and query goes like thid
    END

    With temp tables and SELECT INTO ... MSSQL has no way to know what the heck is the result going to look like unless it actually executes the procedure. Not speaking about you forcing the server to keep on recompiling the procedure.


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Sunday, December 23, 2012 6:18 PM
  • Hi,

    One extra overhead i find here

    ALTER PROC [dbo].[Proc1] 
     (
     @Param1 INT,
     @Param2 INT
     )
     AS
     Begin
     Declare @Temp1 TABLE (Col1 int, Col2 varchar(150));
     

    is that whole table column structure i have to declare. In temporary table there is no such extra overhead.
    But one thing is surprising,why for some SP column information is retreived but for other SP it fails although whole SP definition is almost same except table names and columns fetched.There is no other difference.
    What should i do if i need to use Temporary table in SP.


    • Edited by NorthValley Monday, December 24, 2012 7:22 AM
    Monday, December 24, 2012 7:00 AM
  • Actually there was a runtime error in most of my Stored Procedure and due to that i was getting problem.Ater resolving runtime error in Stored Procedure i was successfully able to retrieve column information in Function import.
    • Edited by NorthValley Monday, December 24, 2012 9:44 AM
    Monday, December 24, 2012 9:44 AM