UDF Error 1

[Mantle51]

Hello,
I'm attempting to convert VBA function into T-SQL but I keep getting error, "Return statements must include scalar valued arguments"

CREATE FUNCTION dbo.fnPrefixZeroAC(@AC_Num varchar(20))
RETURNS varchar(20)

AS
BEGIN
RETURN
If Len(@AC_Num) > 7
fnPrefixZeroAC = Right(@AC_Num, 7)

While Len(@AC_Num) = 7
If Len(@AC_Num) < 7
fnPrefixZeroAC = "0" & @AC_Num
END
END

Thank you

 

[bborissov]

In T-SQL concatenation of the string is done with [+] sign not with [&]:

CREATE FUNCTION dbo.fnPrefixZeroAC(@AC_Num varchar(20))
RETURNS varchar(20)

AS
BEGIN
   RETURN
     If Len(@AC_Num) > 7
     fnPrefixZeroAC = Right(@AC_Num, 7)
   
   While Len(@AC_Num) = 7
        If Len(@AC_Num) < 7
            fnPrefixZeroAC = "0" + @AC_Num
END
END

Whet you try to do? To PAD @AC_Num with zeros up to 7 chars?
If it so then there is better way:

CREATE FUNCTION dbo.fnPrefixZeroAC(@AC_Num varchar(20))
RETURNS varchar(20)

AS
BEGIN
   RETURN RIGHT(REPLICATE('0',7) + @AC_Num, 7)
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mantle51]

Yes that is exactly what I am trying to accomplish. Thank you so much bborissov!

 

[bborissov]

Instead of using a function you always could put that directly in the query. Never made tests but from what I read I think that functions slows down the speed of the query. You could do the tests, betwee:

SELECT RIGHT(REPLICATE('0',7) + Account, 7) AS Account
       FROM YourTable

and

SELECT dbo.fnPrefixZeroAC(Account) AS Account
       FROM YourTable

to see the difference (if there is any ).

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mantle51]

I'll try both ways.