Comment déclarer le tableau en fonction dans SQL Server 2005

J'ai besoin de créer une fonction de certaines opérations de traitement sur les données à renvoyer à une vue SQL.

J'ai conçu la fonction comme ci-dessous, mais je suis d'erreur disant

Doit déclarer la variable de table
"@FINALRESULTS"

bien que je l'ai défini comme table.

Pouvez-vous svp m'aider à faire cette fonction exécutable.

Remercie de votre aide!!

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

OriginalL'auteur Dhiraj | 2011-05-24