函数在Sql查询中指定的参数太多
函数在Sql查询中指定的参数太多
提问于 2014-11-04 17:31:38
在下面的代码中，我有一个函数和一个查询，该函数将返回batchrelease数量。我已经将该函数包含在select too查询中，它抛出错误“Procedure or function dbo.GetBatchReleaseQuantity has too me specified。”.Please帮助我克服这个问题。
SELECT p.ProductID,
p.ProductName,
ISNULL((SELECT ISNULL( CurrentStock,0.00)
FROM Productstatus PS
WHERE PS.ProductID =p.ProductID
AND PS.LocationID = 1
AND PS.StatusDateTime= '2014-08-27'
and PS.productid=p.productid),0) OpeningStockQuantity,
ISNULL((SELECT ISNULL( (CurrentStock*PS.UnitPrice),0.00)
FROM Productstatus PS
WHERE PS.ProductID =p.ProductID
AND PS.LocationID = 1
AND PS.StatusDateTime= '2014-08-27'
and PS.productid=p.productid),0) OpeningStockValue,
ISNULL((SELECT ISNULL( CurrentStock,0.00)
FROM Productstatus PS
WHERE PS.ProductID =p.ProductID
AND PS.LocationID = 1
AND PS.StatusDateTime= '2014-08-27'
and PS.productid=p.productid),0) ClosingStockQuantity,
ISNULL((SELECT ISNULL( (CurrentStock*PS.UnitPrice),0.00)
FROM Productstatus PS
WHERE PS.ProductID =p.ProductID
AND PS.LocationID = 1
AND PS.StatusDateTime= '2014-08-27'
and PS.productid=p.productid),0) ClosingStockValue,
(SELECT dbo.GetBatchReleaseQuantity(1,2, '2014-01-27 00:00:00', '2014-11-27 23:59:59') AS BatchReleaseQuantity
FROM Productstatus PS
WHERE
PS.LocationID = 1
and PS.productid=p.productid AND PS.StatusDateTime > = '2014-01-27 00:00:00' AND PS.StatusDateTime <= '2014-10-27 23:59:59' )
-- ISNULL((SELECT ISNULL( (dbo.GetBatchReleaseQuantity(1,2, '2014-01-27 00:00:00', '2014-11-27 23:59:59')),0.00)
--FROM Productstatus PS
--WHERE PS.ProductID =p.ProductID
-- AND PS.LocationID = 1
-- AND PS.StatusDateTime= '2014-08-27'
-- and PS.productid=p.productid),0) Batchout
FROM Product P
--- SELECT dbo.GetBatchReleaseQuantity(@i_LocationID,P.ProductID,@i_Date,@i_Date) From Product P
LEFT OUTER JOIN LocationProductMap LMP ON LMP.ProductID=P.ProductID
WHERE LMP.ProductInFlow=1函数
ALTER FUNCTION [dbo].[GetBatchReleaseQuantity]
()
RETURNS int
--WITH ENCRYPTION
AS
BEGIN
DECLARE @i_LocationID VARCHAR(50)
DECLARE @i_ProductID INT
DECLARE @i_StartDate VARCHAR(50)
DECLARE @i_EndDate VARCHAR(50)
RETURN (SElECT SUM( BatchReleaseQuantity) AS BatchReleaseQuantity From BatchReleaseDetails BRD
LEFT OUTER JOIN BatchRelease BR ON BR.BatchReleaseID=BRD.BatchReleaseID
Where ProductId=@i_ProductID AND LocationID=@i_LocationID AND BRD.CreatedOn>=@i_StartDate AND BRD.CreatedOn<=@i_EndDate)回答 1
Stack Overflow用户
发布于 2014-11-04 17:36:23
您的函数没有任何参数，您需要将它们放在()中，例如：
CREATE FUNCTION Add
(
@P1 INT,
@P2 INT
)
RETURNS INT
AS
BEGIN
RETUTN @P1 + @P2
END因此，通过将变量改为参数，将您的变量更改为如下所示：
ALTER FUNCTION [dbo].[GetBatchReleaseQuantity]
(
@i_LocationID VARCHAR(50),
@i_ProductID INT,
@i_StartDate VARCHAR(50),
@i_EndDate VARCHAR(50)
)
RETURNS int
--WITH ENCRYPTION
AS
BEGIN
RETURN (SELECT SUM( BatchReleaseQuantity) AS BatchReleaseQuantity From BatchReleaseDetails BRD
LEFT OUTER JOIN BatchRelease BR ON BR.BatchReleaseID=BRD.BatchReleaseID
Where ProductId=@i_ProductID AND LocationID=@i_LocationID AND BRD.CreatedOn>=@i_StartDate AND BRD.CreatedOn<=@i_EndDate)
END此外，您可能需要考虑如何调用它，因为您看起来像是传入了一个INT作为第一个参数，但在函数中，它被声明为VARCHAR(50)。因此，可能需要将@i_LocationID参数设置为INT。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/26731695
复制相关文章
相似问题
腾讯云开发者
