Not able to pass variable value within function

HI,

I am trying to create below function in SQL Server 2005. Howver its giving exception when I am trying to RTrim a value carried in @FULLNAME2 & @FULLNAME1. Exception asking for argument to Rtrim however it has been passed there. Please note I have been able to create this function excluding Rtrim section.

Another issue is I need to call this function in a view , am calling it using select names ('val1','val2'). Its stating this is not a recognised function. However I can see this function in sysobjects table having type 'TF'.

Please suggest.

CREATE FUNCTION dbo.names( @CUSTID varchar(20),@effdt varchar(20))
RETURNS @FinalResults1 TABLE (Name1 nvarchar(254), Name2 nvarchar(254)) 
AS
BEGIN   

DECLARE @FinalResults TABLE  (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
SET @append = ''

SELECT @COUNT = COUNT(*)   FROM @FinalResults 

WHILE @i < @COUNT
BEGIN
IF  @FULLNAME1 = ''  
 BEGIN  
   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



ELSE 
  BEGIN
         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
         BEGIN
         SET    @FREEZENAME1 = 'TRUE';
                IF @FULLNAME2 = ''   
                   BEGIN
                     IF (LEN((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/')<= 40开发者_如何学JAVA AND @FREEZENAME2 = 'FALSE' ) 
                      SET   @FULLNAME2 = ((SELECT NAME254 FROM @FinalResults WHERE SRNO = @i )+ '/') ;
                      ELSE
                      SET   @FREEZENAME2 = 'TRUE';
                   END


ELSE
       BEGIN
                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
       END
       END
       END

IF @append = '' 

           SET @append = (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i );

         Else

           SET @append = @append + '/'+ (SELECT NAME254 FROM @FinalResults WHERE SRNO = @i );



SET @i = @i +1

 If (Len(@append) < 40) 
   SET   @FULLNAME1 = RTrim(@FULLNAME1, '/');
   End
   If ((Len(@append) > 40) And
         (Len(@append) < 80)) 

     SET @FULLNAME2 = RTrim(@FULLNAME2, '/');
   End


INSERT INTO @FinalResults1 VALUES (@FULLNAME1, @FULLNAME2)


RETURN  

END 

the function RTRIM is used to remove whitespace from the right side of a string;

DECLARE @string VARCHAR(20)
SET @string = '  text and text     '
SELECT RTRIM(@string)

This will return the value: ' text and text'

A table valued function needs to be considered a data source in a select statement:

SELECT * from dbo.values('val1', 'val2')

If it is a Table-valued function (returns a table) you should be calling it like this

select <fields> from dbo.name(<parameters>)

Also rtrim functions does not work the way you want it, it expects one arguments and then removes all the spaces to the right of the string

You are also missing a couple of BEGINs in the conditions that call RTRIM, or you could also remove the Ends.

If (Len(@append) < 40) 
BEGIN
   SET   @FULLNAME1 = RTrim(@FULLNAME1, '/');
End
If ((Len(@append) > 40) And (Len(@append) < 80)) 
BEGIN
     SET @FULLNAME2 = RTrim(@FULLNAME2, '/');
End

I looked at your function and the while loop has several odd things, there are sections of the code that will never get executed, i tried to rewrite an equivalent function, i hope this helps you

CREATE FUNCTION dbo.names(@CUSTID varchar(20),@effdt datetime)
RETURNS @results TABLE (Name1 nvarchar(254), Name2 nvarchar(254)) 
AS
BEGIN   
DECLARE rCursor CURSOR FOR
 SELECT (C.NAME1) AS name
 FROM PS_ARB_CU_CLST_STN AS A , PS_ARB_CU_STATIONS AS C 
 WHERE A.EFF_STATUS = 'A' AND 
  A.EFFDT = (
   SELECT MAX(B.EFFDT) 
   FROM PS_ARB_CU_CLST_STN AS 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),
        @fullname2 nvarchar(128),
        @append NVARCHAR (254); 
SET @fullname1 = '';
SET @fullname2 = '';
SET @append = ''

OPEN rCursor;
FETCH NEXT FROM rCursor INTO @name

WHILE @@FETCH_STATUS = 0 AND LEN(@fullname1 + @name) < 40
BEGIN
    SET @fullname1 = @fullname1 + '/' + @name
    FETCH NEXT FROM rCursor INTO @name
END

WHILE @@FETCH_STATUS = 0 AND LEN(@fullname2 + @name) < 40
BEGIN
    SET @fullname2 = @fullname2 + '/' + @name;
    FETCH NEXT FROM rCursor INTO @name;
END

--Append is not used 
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @append = @append + '/' + @name;
    FETCH NEXT FROM rCursor INTO @name;
END

CLOSE rCursor;
DEALLOCATE rCursor;

INSERT INTO @results VALUES (@fullname1, @fullname2)
RETURN
END
GO