How to use temp table in Stored Procedure with LINQ to SQL

Question

I use a temp table in a Stored Procedure with LINQ to SQL. I add the stored procedure to Linq to SQL dbml file then project occur error message

"Unknown Return Type - The return types for the following stored procedures could not be detected.”

When I delete a temp table in a stored procedure then return value is fine.

How can I use a temp table in a stored procedure with Linq to SQL

I replace the temptable like this

  CREATE TABLE tempTable(
PartsReceivingID INT, 
SoPartID INT,
RecvQty INT,
ReturnQty INT
)

Replace like below

  SELECT @RowCount = count(*)           
  FROM Parts.studentTempTable          
   IF @RowCount > 0         
           BEGIN             
             TRUNCATE TABLE Parts.studentTempTable;
           END  

Working version stored procedure

ALTER  PROCEDURE [dbo].[stp_student_Select_New] 
                @pSchID as int, 
                @pCompanyID as int,
                @pAgingDate as int,
                @pTicketNo as VARCHAR(50),
                @pInvoiceNo as VARCHAR(50),
                @pDeliveryNo as VARCHAR(50),
                @pPartNo as VARCHAR(50)
As
SET NOCOUNT ON
BEGIN
          SELECT @RowCount = count(*)
          FROM Parts.studentTempTable

        IF @RowCount > 0
        BEGIN
            TRUNCATE TABLE Parts.studentTempTable;
        END


    ===============================================
    do something with studentTempTable
    ===============================================

        SELECT 
               r.Ticketid AS TicketID,
               r.SoPartNo  AS PartNo ,
               p.Description,
               r.InvoiceNo as InvoiceNo,
               r.InvoiceDate AS InvoiceDate,
               DATEDIFF(DY,r.InvoiceDate,GETDATE())as Aging,
               r.Qty AS CurrentInventory,              
               t.ReturnQty AS ReturnQty
        FROM Parts.studentTempTable AS t,
             Parts.PartsReceiving AS r,
             Parts.PartsInfo as p
        WHERE t.PartsReceivingID = r.PartsReceivingID
          --and i.TicketID = r.TicketID 
          and p.PartID = r.SoPartID  
          and t.ReturnQty >0 
          and DATEDIFF(DY,r.InvoiceDate,GETDATE()) > @pAgingDate
          and r.SchID = @pSchID 
          and r.CompanyID = @pCompanyID
          and r.SoPartNo like  '%%' + @pTicketNo + '%' 
          and r.InvoiceNo like  '%%' + @pInvoiceNo + '%' 
          and r.SoPartNo like  '%%' + @pPartNo + '%' 
          --and i.TicketNo like  '%%' + @pTicketNo + '%' 
          --and r.DeliverNo like  '%%' + @pDeliveryNo + '%' 
Return
END

None Working version stored procedure

ALTER PROCEDURE [dbo].[stp_student_Select] 
                @pVendorID as int, 
                @pCompanyID as int,
                @pAgingDate as int,
                @pTicketNo as VARCHAR(50),
                @pInvoiceNo as VARCHAR(50),
                @pDeliveryNo as VARCHAR(50),
                @pPartNo as VARCHAR(50)

As
SET NOCOUNT ON
BEGIN
    BEGIN TRY


            CREATE TABLE tempTable(
                    PartsReceivingID INT, 
                    SoPartID INT,
                    RecvQty INT,
                    ReturnQty INT
                    )
    ===============================================
    do something with tempTable
    ===============================================
        SELECT 
               isnull(r.Ticketid,0) AS TicketID,
               --i.TicketNo,
               r.SoPartNo  AS PartNo ,
               p.Description,
               r.InvoiceNo as InvoiceNo,
               --r.DeliveryNo,
               r.InvoiceDate AS InvoiceDate,
               DATEDIFF(DY,r.InvoiceDate,GETDATE())as Aging,
               r.Qty AS CurrentInventory,              
               t.ReturnQty AS ReturnQty

        FROM tempTable AS t,
             Parts.PartsReceiving AS r,
             --Ticket.TicketInfo as i,
             Parts.PartsInfo as p

        WHERE t.PartsReceivingID = r.PartsReceivingID
          --and i.TicketID = r.TicketID 
          and p.PartID = r.SoPartID  
          and t.ReturnQty >0 
          and DATEDIFF(DY,r.InvoiceDate,GETDATE()) > @pAgingDate
          and r.VendorID = @pVendorID 
          and r.CompanyID = @pCompanyID
          and r.SoPartNo like  '%%' + @pTicketNo + '%' 
          and r.InvoiceNo like  '%%' + @pInvoiceNo + '%' 
          and r.SoPartNo like  '%%' + @pPartNo + '%' 
          --and i.TicketNo like  '%%' + @pTicketNo + '%' 
          --and r.DeliverNo like  '%%' + @pDeliveryNo + '%' 


        DROP TABLE temptable
    END TRY

    BEGIN CATCH
        SELECT ERROR_MESSAGE() as ErrorMessge,
               ERROR_NUMBER() AS ErrorNumber
    END CATCH
Return
END

Bills

Can you post the working and non-working versions of your stored procs? – Abbottson

Thank you for your comment I attache the stored procs – Bills

Answer 1

If you run the procedure by itself (in SSMS, or Visual Studio) does it return results? Regardless of the answer, I would suggest you use a table variable - what you are currently using is not a temp table - it is just a table. Using a table variable will rule out any issues with actually creating/dropping the table. Googling will find you plenty of information, but this seems to be pretty informative: http://odetocode.com/code/365.aspx

Doscher

Sure thing. BTW, true temp tables are created in the tempdb database and are named with one or two preceding pound signs like #mytemptable. They are often created by selecting from an existing table or tables like: select * into #mytemptable from mytable – Doscher

Thanks for this. I used temp tables in my LINQ implementation and it was quite puzzling why the heck datatype of the sproc could not be determined. Tables variables work OK. – Cletuscleve

So I am not voting this answer down and I have started to see a LOT of people suggesting using table variables instead of temp tables when searching for another answer but that has some very real performance impacts and should NOT be considered a solution for all scenarios especially if table variable will have any sizable amount of data in it. – Plutonium

@Plutonium can you expand on your concern here? The suggestion of the table variable was primarily motivated by OP's errors while creating tables (and he seemed to want something temporary). My research shows that you'd need a pretty large data set (large enough to benefit from an index) before you'd see any real performance differences. Based on the question, it seemed that such concerns may be... out of scope. Please feel free to add detail to the answer if you feel that a warning about performance should be given. – Doscher

@Doscher here's a good discussion on it. #28394 for small data you probably wont notice too much but SQL optimizer (check you execution plans) will benefit by using temp table in many cases over table variables and table variables don't have statistics built on them So even in a small data subset if joining in a complex query you can see benefit to use the temp table. – Plutonium

Answer 2

If tempTable would really have been #tempTable then to auto generate the class for the result set of the stored proc you have to write this at the beginning of the stored proc definition

IF(1=2)
BEGIN
SELECT
  CAST(NULL AS BIGINT)  AS TicketID --assuming TicketId is of bigint type 
  CAST(NULL AS NVARCHAR(16) AS PartNo --assuming PartNo is of Nvarchar(16)
  .......

  END

Disordered

Answer 3

While declaring a table variable will satisfy LINQ to SQL, I have had times where a table variable, due to the lack of statistics, performed horribly.

On those times, I have had to revert to a hack of creating a simple wrapper or shim stored procedure that simply calls the real stored procedure. One requirement of the wrapper stored procedure to make it work is to declare a table variable that matches the output of the real stored procedure and perform an INSERT...EXEC

Declare @Temp table (ColumnA int, ColumnB varchar(256))
Insert Into @Temp(ColumnA, ColumnB)
Exec dbo.OtherStoredProcedure /* pass needed parameters, if any */

Select ColumnA, ColumnB From @Temp

Of course, the definition of the temp table must match exactly the output of the stored procedure. You can't even drop out columns.

LINQ to SQL will not evaluate the "sub" stored procedure at that point and you can call the wrapper stored procedure via LINQ to SQL.

Canny

Answer 4

Add below script in beginning of your SP.

IF 1=0 BEGIN
    SET FMTONLY OFF
END

FMTONLY returns only metadata to the dataset. Can be used to test the format of the response without actually running the query.

Above query will give column output without data.

source: https://www.youtube.com/watch?v=zaL7fbUou7E

Dipsomaniac

Answer 5

create the temp tables used in sp into orignal db as tables and then use these tables in sp after that drop sp in dbml file, it will return the return type of the sp. after you drop the sp in dbml change the original sp to as it was before and delte the temp table form orignal db

Miamiami