How to use a temporary table in a stored procedure with LINQ to SQL

Question

I am using a temporary table in a stored procedure with LINQ to SQL. I add the stored procedure to the Linq to SQL dbml file, then a project error message appears

"Unknown return type - return data types for the following stored procedures could not be detected."

When I delete the temporary table in the stored procedure, then the return value will be fine.

How to use a temporary table in a stored procedure with Linq to SQL

I will replace the seductive, like this

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

Replace as below

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

Working version of the 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 

No working version of the 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 

Answer 1

If you run the procedure yourself (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 temporary table - it is just a table. Using a table variable eliminates any problems with the actual creation / deletion of the table. Googling will find you a lot of information, but it looks pretty informative: http://odetocode.com/code/365.aspx

Answer 2

If tempTable would really be #tempTable, then to automatically create a class for the result set of a stored proc, you must 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 

Answer 3

Although declaring a table variable will satisfy LINQ to SQL, I have had cases where a table variable ran awfully due to lack of statistics.

In those days, I had to go back to creating a hacker procedure or a simple stored procedure that simply calls a real stored procedure. One of the requirements for a stored wrapper procedure for its operation is to declare a table variable that corresponds to the output of the actual stored procedure, and execute INSERT ... EXEC.

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

Of course, the definition of the temporary table must exactly match the output of the stored procedure. You cannot even skip columns.

LINQ to SQL will not evaluate the "sub" stored procedure at this point, and you can invoke the sproc shell through LINQ to SQL.

Answer 4

create temporary tables used in sp in orignal db as tables, then use these tables in sp after this sp sp in dbml file, it will return the return type sp. after you dump sp in dbml, change the original sp to what it was before, and divide the form of the temp orignal db table