?
Solved

Multi Statement table valued function

Posted on 2007-10-06
2
Medium Priority
?
667 Views
Last Modified: 2008-01-09
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
      

     
0
Comment
Question by:venkataramanaiahsr
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 20027021
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
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20028111
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
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question