title: How to declare table in function in SQL Server 2005
body: <p>I need to create a function do to certain processing on data to be returned to a SQL view.</p>
  
  <p>I have designed the function as below, but am getting error saying</p>
  
  <blockquote>
    <p>Must declare the table variable
    "@FINALRESULTS"</p>
  </blockquote>
  
  <p>although I have defined it as table.</p>
  
  <p>Can you please help me making this function executable.</p>
  
  <p>Appreciate your help!!</p>
  
  <pre><code>CREATE FUNCTION dbo.names(@CUSTID varchar(20), @effdt varchar(20))
  RETURNS @FinalResults1 (Name1 nvarchar(254), Name2 nvarchar(254))
  AS
  BEGIN   
    DECLARE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))
  
    CREATE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))
  
    INSERT INTO @FinalResults
       SELECT(C.NAME1),ROW_NUMBER() OVER(ORDER BY A.SEQ_NBR) 
       FROM PS_ARB_CU_CLST_STN A , PS_ARB_CU_STATIONS C 
       WHERE A.EFF_STATUS = 'A' 
         AND A.EFFDT = (SELECT MAX(B.EFFDT) 
                        FROM PS_ARB_CU_CLST_STN B 
                        WHERE A.SETID = B.SETID 
                          AND A.CUST_ID = B.CUST_ID 
                          AND B.EFFDT &lt;= @effdt) 
         AND A.SETID = C.SETID 
         AND A.ARB_STATION_ID =C.CUST_ID 
         AND A.CUST_ID = @CUSTID
         AND C.EFFDT = (SELECT MAX(D.EFFDT) 
                        FROM PS_ARB_CU_STATIONS D 
                        WHERE C.CUST_ID = D.CUST_ID
                          AND D.SETID = C.SETID 
                          AND D.EFFDT &lt;= @effdt)
   ORDER BY 
      A.SEQ_NBR
  
  DECLARE @Name nvarchar(254), @FULLNAME1 nvarchar(128), @FREEZENAME1 nvarchar(10), @append NVARCHAR (254)
  DECLARE @FULLNAME254 nvarchar(254), @FULLNAME2 nvarchar(128), @FREEZENAME2 nvarchar(10), @COUNT INT, @i INT
  
  SET @Name = ''
  SET @FREEZENAME1 = 'FALSE'
  SET @FREEZENAME2 = 'FALSE'
  SET @FULLNAME1 = ''
  SET @FULLNAME2 = ''
  SET @FULLNAME254 = ''
  SET @COUNT = 0
  SET @i  = 0
  
  SELECT @COUNT = COUNT(*) FROM @FinalResults 
  
  WHILE @i &lt; @COUNT
  BEGIN
    IF  @FULLNAME1 = ''  
      IF(LEN((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/')&lt;= 40 AND @FREEZENAME1 = 'FALSE' )
         SET @FULLNAME1 = (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i  + '/');
      ELSE
         SET @FREEZENAME1 = 'TRUE';
      END IF
    ELSE 
      IF (LEN(@FULLNAME1 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') &lt;=40 AND @FREEZENAME1 = 'FALSE' )
        SET @FULLNAME1 = (@FULLNAME1 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') ;
      ELSE
        SET @FREEZENAME1 = 'TRUE';
  
      IF @FULLNAME2 = ''   
         IF (LEN((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/')&lt;= 40 AND @FREEZENAME2 = 'FALSE' ) 
            SET @FULLNAME2 = ((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') ;
         ELSE
            SET @FREEZENAME2 = 'TRUE';
         END IF
      ELSE
         IF (LEN(@FULLNAME2 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') &lt;=40 AND @FREEZENAME2 = 'FALSE') 
      SET @FULLNAME2 = (@FULLNAME2 +(SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') ;
         ELSE
      SET    @FREEZENAME2 = 'TRUE';
  
         END-IF
         END-IF
         END-IF
         END-IF
  
  IF @append = '' 
              @append = (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i );
           Else
              @append = @append + '/'+ (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i );
            END-IF
  
       END-IF
  SET @i = @i +1
  END
  END-WHILE
  
    If (Len(@append) &lt; 40) 
        @FULLNAME1 = RTrim(@FULLNAME2, '/');
     End-If;
     If ((Len(@append) &gt; 40) And
           (Len(@append) &lt; 80)) 
  
        @FULLNAME2 = RTrim(@FULLNAME2, '/');
     End-If;
  
  BEGIN
  INSERT INTO #FinalResults1 VALUES ( @FULLNAME1, @FULLNAME2)
  END
  RETURN 
  
  END 
  
  GO 
   SELECT Name1 
   , Name2 
    FROM @FinalResults1
  </code></pre>