gpt4 book ai didi

linq-to-sql - 如何通过 LINQ to SQL 在存储过程中使用临时表

转载 作者:行者123 更新时间:2023-12-03 00:02:34 25 4
gpt4 key购买 nike

我在 LINQ to SQL 的存储过程中使用临时表。我将存储过程添加到 Linq to SQL dbml 文件,然后项目出现错误消息

“未知返回类型 - 无法检测到以下存储过程的返回类型。”

当我删除存储过程中的临时表时,返回值就可以了。

如何使用 Linq to SQL 在存储过程中使用临时表

我像这样替换了temptable

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

替换如下

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

工作版本存储过程

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

无工作版本存储过程

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

最佳答案

如果您单独运行该过程(在 SSMS 或 Visual Studio 中),它会返回结果吗?无论答案如何,我建议您使用表变量 - 您当前使用的不是临时表 - 它只是一个表。使用表变量将排除实际创建/删除表时出现的任何问题。谷歌搜索会找到大量信息,但这似乎信息量很大:http://odetocode.com/code/365.aspx

关于linq-to-sql - 如何通过 LINQ to SQL 在存储过程中使用临时表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5488730/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com