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].[fnDeptDailyStkLedge r] Script Date: 10/06/2007 13:38:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnConsolidatedDeptD ailyStkLed ger]
(
@Dept varchar(2),
@Branch varchar(2),
@FDate varchar(8),
@Tdate varchar(8)
)
RETURNS @DeptwiseConsolidatedDaily StkLedger 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 @DeptwiseConsolidatedDaily StkLedger
select @Fdate,S.* from fnDeptDailyStkLedger(@Dept ,@Branch,@ Fdate) as S
set @Fdate = @Fdate + 1
End
GO
/****** Object: UserDefinedFunction [dbo].[fnDeptDailyStkLedge
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnConsolidatedDeptD
(
@Dept varchar(2),
@Branch varchar(2),
@FDate varchar(8),
@Tdate varchar(8)
)
RETURNS @DeptwiseConsolidatedDaily
(
@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 @DeptwiseConsolidatedDaily
select @Fdate,S.* from fnDeptDailyStkLedger(@Dept
set @Fdate = @Fdate + 1
End
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE FUNCTION [dbo].[fnConsolidatedDeptD
(
@Dept varchar(2),
@Branch varchar(2),
@FDate varchar(8),
@Tdate varchar(8)
)
RETURNS @DeptwiseConsolidatedDaily
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 @DeptwiseConsolidatedDaily
select @Fdate,S.* from fnDeptDailyStkLedger(@Dept
set @Fdate = @Fdate + 1
RETURN
End