sql - SQL Server 2005 如何在函数中声明表

Question

我需要创建一个函数来对要返回到 SQL View 的数据进行某些处理。

我设计了如下的函数，但是我得到了错误提示

Must declare the table variable "@FINALRESULTS"

虽然我已经把它定义为表格了。

你能帮我使这个函数可执行吗。

感谢您的帮助!!

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 <= @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 <= @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 < @COUNT
BEGIN
  IF  @FULLNAME1 = ''  
    IF(LEN((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/')<= 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 )+ '/') <=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 )+ '/')<= 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 )+ '/') <=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) < 40) 
      @FULLNAME1 = RTrim(@FULLNAME2, '/');
   End-If;
   If ((Len(@append) > 40) And
         (Len(@append) < 80)) 

      @FULLNAME2 = RTrim(@FULLNAME2, '/');
   End-If;

BEGIN
INSERT INTO #FinalResults1 VALUES ( @FULLNAME1, @FULLNAME2)
END
RETURN 

END 

GO 
 SELECT Name1 
 , Name2 
  FROM @FinalResults1

Answer 1

DECLARE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))


CREATE TABLE @FinalResults (Name254 nvarchar(254), SRNO nvarchar(3))

应该只是

DECLARE @FinalResults TABLE  (Name254 nvarchar(254), SRNO nvarchar(3))

此外函数的返回类型应该是

RETURNS @FinalResults1 TABLE (Name1 nvarchar(254), Name2 nvarchar(254))

似乎还有更多语法错误(您为什么使用 END-IF？)但这应该可以回答所问的问题，我无意修复所有错误。