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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With