SQL Table valued function

Question

I have a Table Valued Function as following. But it is giving a error. That is saying "Msg 156, Level 15, State 1, Procedure GetOpeningByRepAcc, Line 36 Incorrect syntax near the keyword 'begin'."

But I didn't able to fix this. Any idea please?

create FUNCTION [dbo].[GetOpeningByRepAcc]
(
 @Date datetime,@AccId int,@CenterId datetime,@ItemId int
)
RETURNS @ReturnData TABLE
(
Opening decimal(10,3),
Topup decimal(10,3),
Returnsv decimal(10,3)
)
as
begin   
declare @opening decimal(10,3)
declare @topup decimal(10,3)
declare @returns decimal(10,3)

set @opening = (SELECT sum(OpeningQty)
FROM DailyItemStock
WHERE DistributionCenterId = @CenterId AND 
SalesRepAccId = @AccId AND ItemMasterId = @ItemId
and convert(varchar(10),StockDate,111) = convert(varchar(10),@Date,111))

set @topup = (SELECT     sum(Qty)
FROM         DailyItemStockTopUp
WHERE     
convert(varchar(10),TopupDate,111) = CONVERT(varchar(10), getdate(), 111)
AND DistributionCenterId = @CenterId AND SalesRepAccId = @AccId 
AND ItemMasterId =      @ItemId)

set @returns = (SELECT sum(TotReturns)
FROM DailyItemStock
WHERE DistributionCenterId = @CenterId AND 
SalesRepAccId = @AccId AND ItemMasterId = @ItemId
and convert(varchar(10),StockDate,111) = convert(varchar(10),@Date,111))
end

begin
insert @ReturnData
select @opening,@topup,@returns

return 
end

GarethD · Accepted Answer · 2015-01-02T11:01:52.730

You have two lots of BEGIN... END in your function this is causing the error. Looking at your function though it can be simplified hugely, you are performing pretty much the same query 3 times, just summing different columns, two of them are:

SET @Topup = (  SELECT  SUM(Qty)
                FROM    DailyItemStock
                WHERE   DistributionCenterId = @CenterId 
                AND     SalesRepAccId = @AccId 
                AND     ItemMasterId = @ItemId
                AND     CONVERT(VARCHAR(10),TopupDate,111) = CONVERT(VARCHAR(10), GETDATE(), 111)
            );

SET @returns = (SELECT  SUM(TotReturns)
                FROM    DailyItemStock
                WHERE   DistributionCenterId = @CenterId 
                AND     SalesRepAccId = @AccId 
                AND     ItemMasterId = @ItemId
                AND     CONVERT(VARCHAR(10),TopupDate,111) = CONVERT(VARCHAR(10), GETDATE(), 111)
            );

You can do this in a single statement:

SELECT  @Topup = SUM(Qty),
        @returns = SUM(TotReturns)
FROM    DailyItemStock
WHERE   DistributionCenterId = @CenterId 
AND     SalesRepAccId = @AccId 
AND     ItemMasterId = @ItemId
AND     CAST(StockDate AS DATE) = CAST(GETDATE() AS DATE);

n.b. I have changed your predicate converting dates to varchars to compare them (I assume to remove the time) as this is awful practice, it performs terribly and can't use any indexes on the date columns

With the above in mind, I would be inclined to make this an inline TVF, it will perform much better:

CREATE FUNCTION [dbo].[GetOpeningByRepAcc]
(
    @Date       DATETIME,
    @AccId      INT,
    @CenterId   DATETIME,
    @ItemId     INT
)
RETURNS TABLE 
AS
RETURN
(   SELECT  Opening = SUM(OpeningQty),
            Topup = SUM(Qty),
            Returnsv = SUM(TotReturns)
    FROM    DailyItemStock
    WHERE   DistributionCenterId = @CenterId 
    AND     SalesRepAccId = @AccId 
    AND     ItemMasterId = @ItemId
    AND     CAST(StockDate AS DATE) = CAST(GETDATE() AS DATE)
);

The benefit of inline Table valued functions is that they behave more like views, in that their definition can be expanded out into the outer query and subsequently optimised, and are not executed RBAR like functions that use BEGIN...END

score 0 · Answer 2 · answered Jan 02 '15 at 10:45

I think that you have one extra end ... begin. Please try the following version of you function:

create FUNCTION [dbo].[GetOpeningByRepAcc]
(
 @Date datetime,@AccId int,@CenterId datetime,@ItemId int
)
RETURNS @ReturnData TABLE
(
Opening decimal(10,3),
Topup decimal(10,3),
Returnsv decimal(10,3)
)
as
begin   
    declare @opening decimal(10,3)
    declare @topup decimal(10,3)
    declare @returns decimal(10,3)

    set @opening = (SELECT sum(OpeningQty)
    FROM DailyItemStock
    WHERE DistributionCenterId = @CenterId AND 
    SalesRepAccId = @AccId AND ItemMasterId = @ItemId
    and convert(varchar(10),StockDate,111) = convert(varchar(10),@Date,111))

    set @topup = (SELECT     sum(Qty)
    FROM         DailyItemStockTopUp
    WHERE     
    convert(varchar(10),TopupDate,111) = CONVERT(varchar(10), getdate(), 111)
    AND DistributionCenterId = @CenterId AND SalesRepAccId = @AccId 
    AND ItemMasterId =      @ItemId)

    set @returns = (SELECT sum(TotReturns)
    FROM DailyItemStock
    WHERE DistributionCenterId = @CenterId AND 
    SalesRepAccId = @AccId AND ItemMasterId = @ItemId
    and convert(varchar(10),StockDate,111) = convert(varchar(10),@Date,111))

    insert into @ReturnData
    select @opening,@topup,@returns

    return 
end

score 0 · Answer 3 · answered Jan 02 '15 at 10:48

BEGIN and END are not properly closed:

CREATE FUNCTION [dbo].[Getopeningbyrepacc] (@Date     DATETIME,
                                            @AccId    INT,
                                            @CenterId DATETIME,
                                            @ItemId   INT)
RETURNS @ReturnData TABLE (
  Opening  DECIMAL(10, 3),
  Topup    DECIMAL(10, 3),
  Returnsv DECIMAL(10, 3))
AS
  BEGIN
      DECLARE @opening DECIMAL(10, 3)
      DECLARE @topup DECIMAL(10, 3)
      DECLARE @returns DECIMAL(10, 3)

      SET @opening = (SELECT Sum(OpeningQty)
                      FROM   DailyItemStock
                      WHERE  DistributionCenterId = @CenterId
                             AND SalesRepAccId = @AccId
                             AND ItemMasterId = @ItemId
                             AND CONVERT(VARCHAR(10), StockDate, 111) = CONVERT(VARCHAR(10), @Date, 111))
      SET @topup = (SELECT Sum(Qty)
                    FROM   DailyItemStockTopUp
                    WHERE  CONVERT(VARCHAR(10), TopupDate, 111) = CONVERT(VARCHAR(10), Getdate(), 111)
                           AND DistributionCenterId = @CenterId
                           AND SalesRepAccId = @AccId
                           AND ItemMasterId = @ItemId)
      SET @returns = (SELECT Sum(TotReturns)
                      FROM   DailyItemStock
                      WHERE  DistributionCenterId = @CenterId
                             AND SalesRepAccId = @AccId
                             AND ItemMasterId = @ItemId
                             AND CONVERT(VARCHAR(10), StockDate, 111) = CONVERT(VARCHAR(10), @Date, 111))

      BEGIN
          INSERT @ReturnData
          SELECT @opening,
                 @topup,
                 @returns

          RETURN
      END
  END

SQL Table valued function

3 Answers3