Function has too many arguments specified in Sql query

Question

In the below code i have a function and a query the function will return batchrelease quantity.I have include the function in the select sqlquery it throws a error"Procedure or function dbo.GetBatchReleaseQuantity has too many arguments specified.".Please help me to overcome this issue.

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

Function

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)

Looks to me like the variable decs in the function are in the wrong place. — Fred
– Fred, Commented Nov 4, 2014 at 9:36

DavidG · Accepted Answer · 2014-11-04 09:36:22Z

4

Your function doesn't have any parameters, you need to put them in the (), for example:

CREATE FUNCTION Add
(
    @P1 INT,
    @P2 INT
)
RETURNS INT
AS
BEGIN
    RETUTN @P1 + @P2
END

So change yours to something like this by moving the variables to be parameters instead:

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

Also you probably need to think about how you are calling it as you appear to be passing in an INT as the first parameter but in the function it's declared as a VARCHAR(50). So it may need to be that your @i_LocationID parameter should be an INT.

answered Nov 4, 2014 at 9:36

DavidG

120k13 gold badges232 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Developer Over a year ago

when i execute the above query it throws subquery return more than one value

DavidG Over a year ago

That's a problem with your stored procedure and is another problem completely. Deconstruct the query into parts that work and join them together one by one.

Collectives™ on Stack Overflow

Function has too many arguments specified in Sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related