Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UDF Error 1

Status
Not open for further replies.

Mantle51

Programmer
Aug 17, 2006
97
US
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
 
In T-SQL concatenation of the string is done with [+] sign not with [&]:
Code:
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:
Code:
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
 
Yes that is exactly what I am trying to accomplish. Thank you so much 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:
Code:
SELECT RIGHT(REPLICATE('0',7) + Account, 7) AS Account
       FROM YourTable

and
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top