Link to home
Start Free TrialLog in
Avatar of venkataramanaiahsr
venkataramanaiahsr

asked on

Multi Statement table valued function

I have writtern the following function which is throwing up incorrect syntax . can some body pls show me what is wrong

GO
/****** Object:  UserDefinedFunction [dbo].[fnDeptDailyStkLedger]    Script Date: 10/06/2007 13:38:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 CREATE FUNCTION [dbo].[fnConsolidatedDeptDailyStkLedger]
(
       @Dept varchar(2),      
         @Branch   varchar(2),
         @FDate       varchar(8),
         @Tdate    varchar(8)         
)

RETURNS @DeptwiseConsolidatedDailyStkLedger Table

      (
        @Trandate char(8),
         @DeptCode char(2),
        @DeptName char(2),       
        @OB_Qty   int,
        @OB_Value int,
        @Pur_Qty  int,
        @Pur_Value int,
        @RCT_Qty       int,
        @RCT_Value int,
        @ADJ(+)_Qty int,
        @ADJ(+)_Value int,
        @ISS_Qty int,
        @ISS_Value int,
        @ADJ(-)_Qty int,
        @ADJ(-)_Value int,
        @DIS_Qty int,
        @DIS_Value int,
        @PUR_RET_Qty int,
        @PUR_RET_Value int
   )
AS
Begin
   While @Fdate <= @Tdate    
     Insert @DeptwiseConsolidatedDailyStkLedger
     select @Fdate,S.*  from fnDeptDailyStkLedger(@Dept,@Branch,@Fdate) as S
     set @Fdate = @Fdate + 1      
End
      

     
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
just add a return statement at the bottom

CREATE FUNCTION [dbo].[fnConsolidatedDeptDailyStkLedger]
(
       @Dept varchar(2),      
         @Branch   varchar(2),
         @FDate       varchar(8),
         @Tdate    varchar(8)        
)
RETURNS @DeptwiseConsolidatedDailyStkLedger Table (
        Trandate char(8),
         DeptCode char(2),
        DeptName char(2),        
        OB_Qty   int,
        OB_Value int,
        Pur_Qty  int,
        Pur_Value int,
        RCT_Qty       int,
        RCT_Value int,
        [ADJ(+)_Qty] int,
        [ADJ(+)_Value] int,
        ISS_Qty int,
        ISS_Value int,
        [ADJ(-)_Qty] int,
        [ADJ(-)_Value] int,
        DIS_Qty int,
        DIS_Value int,
        PUR_RET_Qty int,
        PUR_RET_Value int
   )
AS
Begin
   While @Fdate <= @Tdate    
     Insert @DeptwiseConsolidatedDailyStkLedger
     select @Fdate,S.*  from fnDeptDailyStkLedger(@Dept,@Branch,@Fdate) as S
     set @Fdate = @Fdate + 1

RETURN
     
End