title: error at the "return" statement
body: <p>I have written a table valued function in sql server and facing an error at the "return statement"
  All the statements in the code are correct only, but i don't know why this error</p>
  
  <p>my error is </p>
  
  <pre><code>Incorrect syntax near the keyword 'RETURN'.
  </code></pre>
  
  <p>my sql function is </p>
  
  <pre><code>ALTER FUNCTION [dbo].[WEEKLY_REPORT](@START_DATE DATETIME, @END_DATE DATETIME,@PRACTISE NVARCHAR(MAX))
  RETURNS @WEEKLY_REPORT_DETAILS_INFORMATION TABLE
  (
  RESOURCE_NAME NVARCHAR(MAX),
  DAY1 DECIMAL(5,2),
  DAY2 DECIMAL(5,2),
  DAY3 DECIMAL(5,2),
  DAY4 DECIMAL(5,2),
  DAY5 DECIMAL(5,2),
  DAY6 DECIMAL(5,2),
  DAY7 DECIMAL(5,2),
  UTILIZATION INT
  )
  AS 
  BEGIN
  DECLARE
      @RESOURCE_NAME NVARCHAR(MAX),
      @DAY1 DECIMAL(5,2),
      @DAY2 DECIMAL(5,2),
      @DAY3 DECIMAL(5,2),
      @DAY4 DECIMAL(5,2),
      @DAY5 DECIMAL(5,2),
      @DAY6 DECIMAL(5,2),
      @DAY7 DECIMAL(5,2),
      @UTILIZATION INT,
      @LAST_DAY1 INT,
  @MONTH NVARCHAR(MAX),
  @START_DAY INT,
  @END_DAY INT,
  @COUNT INT,
  @COUNT_TEST INT,
  @QUERY NVARCHAR(MAX);
  BEGIN
      SET @MONTH = DATENAME(MONTH,@START_DATE)
      SET @START_DAY = DATENAME(DAY,@START_DATE)
      SET @END_DAY = DATENAME(DAY,@END_DATE)
      SET @COUNT = @END_DAY -@START_DAY + 1
  --  SET @QUERY='SELECT A.NAME, '
  
  --SET @QUERY = ' INSERT INTO @WEEKLY_REPORT_DETAILS_INFORMATION(RESOURCE_NAME,DAY1,DAY2,DAY3,
  --              DAY4,DAY5,DAY6,DAY7,UTILIZATION) SELECT A.NAME,'
  CREATE TABLE TEMP(RESOURCE_NAME NVARCHAR(MAX), DAY1 DECIMAL(5,2),
      DAY2 DECIMAL(5,2),
      DAY3 DECIMAL(5,2),
      DAY4 DECIMAL(5,2),
      DAY5 DECIMAL(5,2),
  DAY6 DECIMAL(5,2),
  DAY7 DECIMAL(5,2))
  
  SET @QUERY = ' INSERT INTO #TEMP(RESOURCE_NAME,DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,DAY7)' +
               ' SELECT A.NAME,'
      SET @COUNT_TEST = 1
  WHILE @COUNT_TEST &lt;= @COUNT
  BEGIN
      IF(DATEPART(MONTH,@START_DATE)=DATEPART(MONTH,@END_DATE))
      BEGIN
          IF(@COUNT_TEST =@COUNT)
      BEGIN
          SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@START_DAY)
          SET @START_DAY = @START_DAY + 1
      END
      ELSE
      BEGIN
          SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@START_DAY) + ', ' 
          SET @START_DAY = @START_DAY + 1
      END
      SET @COUNT_TEST =@COUNT_TEST + 1    
  END
  ELSE 
  BEGIN
      IF( DATEPART(MONTH,@START_DATE) = 1 OR DATEPART(MONTH,@START_DATE) = 3 OR
          DATEPART(MONTH,@START_DATE) = 5 OR DATEPART(MONTH,@START_DATE) = 7 OR
          DATEPART(MONTH,@START_DATE) = 8 OR DATEPART(MONTH,@START_DATE) = 10 OR
          DATEPART(MONTH,@START_DATE) = 12 )
      BEGIN   
          SET @LAST_DAY1 = DATENAME(DAY,@START_DATE)
          WHILE (@LAST_DAY1 &lt;= 31 )
          BEGIN
              IF(@COUNT_TEST =@COUNT)
                  SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@LAST_DAY1)
              ELSE
                  SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@LAST_DAY1) + ', '
          SET @COUNT_TEST =@COUNT_TEST + 1 
          SET @LAST_DAY1 = @LAST_DAY1 +1  
          END
          SET @LAST_DAY1 = DATENAME(DAY,@END_DATE) - (DATENAME(DAY,@END_DATE)-1)
          WHILE (@LAST_DAY1 &lt;=  DATENAME(DAY,@END_DATE))
          BEGIN
              IF(@COUNT_TEST =@COUNT)
                  SET @QUERY=@QUERY + 'C.D' + CONVERT(NVARCHAR ,@LAST_DAY1)
              ELSE
                  SET @QUERY=@QUERY + 'C.D' + CONVERT(NVARCHAR ,@LAST_DAY1) + ', '
          SET @COUNT_TEST =@COUNT_TEST + 1
          SET @LAST_DAY1 = @LAST_DAY1 +1      
          END
      END
      ELSE 
      IF( DATEPART(MONTH,@START_DATE) = 4 OR DATEPART(MONTH,@START_DATE) = 6 OR
               DATEPART(MONTH,@START_DATE) = 9 OR DATEPART(MONTH,@START_DATE) = 11 )
      BEGIN       
          SET @LAST_DAY1 = DATENAME(DAY,@START_DATE)
          WHILE (@LAST_DAY1 &lt;= 30 )
          BEGIN
              IF(@COUNT_TEST =@COUNT)
                  SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@LAST_DAY1)       
              ELSE
                  SET @QUERY=@QUERY + 'C.D' + CONVERT(NVARCHAR ,@LAST_DAY1) + ', '
          SET @COUNT_TEST =@COUNT_TEST + 1
          SET @LAST_DAY1 = @LAST_DAY1 +1      
          END
          SET @LAST_DAY1 = DATENAME(DAY,@END_DATE) - (DATENAME(DAY,@END_DATE)-1)
          WHILE (@LAST_DAY1 &lt;=  DATENAME(DAY,@END_DATE))
          BEGIN
              IF(@COUNT_TEST =@COUNT)
                  SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@LAST_DAY1)
              ELSE
                  SET @QUERY=@QUERY + 'C.D' + CONVERT(NVARCHAR ,@LAST_DAY1) + ', '
          SET @COUNT_TEST =@COUNT_TEST + 1
          SET @LAST_DAY1 = @LAST_DAY1 +1      
          END
      END
      ELSE
      BEGIN
          SET @LAST_DAY1 = DATENAME(DAY,@START_DATE)
          WHILE (@LAST_DAY1 &lt;= 28 )
          BEGIN
              IF(@COUNT_TEST =@COUNT)
                  SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@LAST_DAY1)
              ELSE
                  SET @QUERY=@QUERY + 'C.D' + CONVERT(NVARCHAR ,@LAST_DAY1) + ', '
          SET @COUNT_TEST =@COUNT_TEST + 1
          SET @LAST_DAY1 = @LAST_DAY1 +1   
          END
          SET @LAST_DAY1 = DATENAME(DAY,@END_DATE) - (DATENAME(DAY,@END_DATE)-1)
          WHILE (@LAST_DAY1 &lt;=  DATENAME(DAY,@END_DATE))
          BEGIN
              IF(@COUNT_TEST =@COUNT)
                  SET @QUERY=@QUERY + 'B.D' + CONVERT(NVARCHAR ,@LAST_DAY1)
              ELSE
                  SET @QUERY=@QUERY + 'C.D' + CONVERT(NVARCHAR ,@LAST_DAY1) + ', '
          SET @COUNT_TEST =@COUNT_TEST + 1
          SET @LAST_DAY1 = @LAST_DAY1 +1   
          END
      END
  END 
  END
  
  IF(DATENAME(MONTH,@START_DATE)=DATENAME(MONTH,@END_DATE))
  BEGIN
      SET @QUERY = @QUERY + ' FROM EMPLOYEE A JOIN CP_DETAILS B' +
                  ' ON A.ID = B.EMPLOYEE_ID WHERE B.MONTH_USED = ' + '''' + @MONTH + '''' +
                  ' AND B.YEAR_USED = ' + DATENAME(YEAR,@START_DATE) + 
                  ' AND B.PRACTISE=' + '''' + @PRACTISE + ''''
  END
  ELSE
  BEGIN
      SET @QUERY = @QUERY + ' FROM EMPLOYEE A JOIN CP_DETAILS B' +
                  ' ON A.ID = B.EMPLOYEE_ID JOIN CP_DETAILS C ON A.ID=C.EMPLOYEE_ID' +
                  ' WHERE B.MONTH_USED = ' + '''' + DATENAME        (MONTH,@START_DATE) + '''' +
                  ' AND B.YEAR_USED = ' + DATENAME(YEAR,@START_DATE) + 
                  ' AND B.PRACTISE=' + '''' + @PRACTISE + '''' + 
                  ' AND C.MONTH_USED=' + '''' + DATENAME(MONTH,@END_DATE) + ''''
  END
  
  EXEC SP_EXECUTESQL @QUERY
  
  DECLARE CP_DETAILS CURSOR FOR SELECT * FROM TEMP
  
  OPEN CP_DETAILS
  FETCH NEXT FROM CP_DETAILS INTO @RESOURCE_NAME,@DAY1,@DAY2,@DAY3,@DAY4,@DAY5,@DAY6,@DAY7
  
  WHILE (@@FETCH_STATUS =0)
  BEGIN
      SET @UTILIZATION = dbo.UTILIZATION(@START_DATE,@END_DATE,@DAY1,
                                      @DAY2,@DAY3,@DAY4,@DAY5,@DAY6,@DAY7)
      INSERT INTO @WEEKLY_REPORT_DETAILS_INFORMATION VALUES(@RESOURCE_NAME,
                  @DAY1,@DAY2,@DAY3,@DAY4,@DAY5,@DAY6,@DAY7,@UTILIZATION)
  
      FETCH NEXT FROM CP_DETAILS INTO @RESOURCE_NAME,@DAY1,@DAY2,@DAY3,@DAY4,@DAY5,@DAY6,@DAY7
  END
  CLOSE CP_DETAILS
  DEALLOCATE CP_DETAILS
  DROP TEMP
  RETURN ---error here
  END
  END
  </code></pre>