February 17, 2009 at 8:55 am
how can i convert this to a sql function
Function GetPrefixForCustomer(CurrentValue)
Option Explicit
Dim sRet
Dim iRemainder
Dim iQuotient
Dim intArray()
sRet = ""
iRemainder = 0
iQuotient = 0
If CurrentValue > 0 Then
intArray = Split((CurrentValue / 26),".")
iQuotient = intArray(0)
iRemainder = CurrentValue Mod 26
If iRemainder = 0 Then
iQuotient = iQuotient - 1
iRemainder = 26
End If
sRet = Chr$(CInt(65 + iRemainder - 1))
sRet = GetPrefixForCustomer(iQuotient) & sRet
End If
RETURN sRet
End Function
February 17, 2009 at 10:27 am
how can i run the script as a sql job against a database and table?
Function GetPrefixForCustomer(CurrentValue)
Option Explicit
Dim sRet
Dim iRemainder
Dim iQuotient
Dim intArray()
sRet = ""
iRemainder = 0
iQuotient = 0
If CurrentValue > 0 Then
intArray = Split((CurrentValue / 26),".")
iQuotient = intArray(0)
iRemainder = CurrentValue Mod 26
If iRemainder = 0 Then
iQuotient = iQuotient - 1
iRemainder = 26
End If
sRet = Chr$(CInt(65 + iRemainder - 1))
sRet = GetPrefixForCustomer(iQuotient) & sRet
End If
RETURN sRet
End Function
February 17, 2009 at 11:26 am
This UDF should do the same as your VBScript.
It is written for integers of type bigint, but will also work with integers of type int.
I have used a WHILE loop as it is likely to be more efficient than function recursion.
CREATE FUNCTION dbo.GetPrefixForCustomer (@CurrentValue bigint)
RETURNS varchar(14)
WITH SCHEMABINDING
AS BEGIN
DECLARE @buffer varchar(14)
DECLARE @rem bigint
SELECT @buffer = '', @rem = @CurrentValue
WHILE (@rem > 0) BEGIN
SELECT @buffer = CHAR((@rem - 1) % 26 + 65) + @buffer, @rem = (@rem - 1) / 26
END
RETURN @buffer
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply