vb function to sql svr

  • Does someone want to tackle this? I have a vb program that calculate a 9th digit where i need 8. Need this in sql server sp

    Sub CheckDigit()

    Dim Cusip_Array(1 To 8) As Integer

    Dim tmp_digit, tmp_resultstring, CusipCheckDigit As String

    Dim k, i As Long

    Dim sum_digits, tmp_result, PositionChar As Long

    Dim tmp_resultleft As Integer

    tmp_digit = tmp_CUSIP

    k = 1

    Do While Len(tmp_digit) >= 1

    ' make a call to the SOMETHING to find the digital equivalent

    Cusip_Array(k) = DigitalEquivalent(Left$(tmp_digit, 1))

    k = k + 1

    tmp_digit = Mid$(tmp_CUSIP, k)

    Loop

    ' Doubling the second, fourth, sixth, and eighth digital equivalents.

    Cusip_Array(2) = Cusip_Array(2) * 2

    Cusip_Array(4) = Cusip_Array(4) * 2

    Cusip_Array(6) = Cusip_Array(6) * 2

    Cusip_Array(8) = Cusip_Array(8) * 2

    ' Adding all the tens digits together and adding all the ones digits together, then comining the sums.

    sum_digits = 0

    For i = 1 To 8

    ' add the ones......

    If Cusip_Array(i) >= 1 And Cusip_Array(i) <= 9 Then

    sum_digits = sum_digits + Cusip_Array(i)

    Else

    sum_digits = sum_digits + CInt(Left$(CStr(Cusip_Array(i)), 1))

    sum_digits = sum_digits + CInt(Right$(CStr(Cusip_Array(i)), 1))

    End If

    Next

    ' Dividing the previous sum by 10. If the result has no factional part or remainder, the check digit is zero. Otherwise, the check

    ' digit equals the result plus one, times ten, minus the original sum.

    ' (Check digit = ( result + 1 ) * 10 - Original sum )

    tmp_result = (sum_digits / 10)

    tmp_resultstring = CStr(tmp_result)

    PositionChar = InStr(tmp_resultstring, ".")

    If PositionChar = 0 Then

    CusipCheckDigit = "0"

    Else

    tmp_resultleft = CInt(Left$(tmp_resultstring, PositionChar))

    CusipCheckDigit = CStr(((tmp_resultleft + 1) * 10) - sum_digits)

    End If

    tmp_CUSIP = tmp_CUSIP & CusipCheckDigit

    exit1:

    End Sub

    Function DigitalEquivalent(tmp_send As String) As Integer

    Select Case tmp_send

    Case "0"

    tmp_return = 0

    Case "1"

    tmp_return = 1

    Case "2"

    tmp_return = 2

    Case "3"

    tmp_return = 3

    Case "4"

    tmp_return = 4

    Case "5"

    tmp_return = 5

    Case "6"

    tmp_return = 6

    Case "7"

    tmp_return = 7

    Case "8"

    tmp_return = 8

    Case "9"

    tmp_return = 9

    Case "A"

    tmp_return = 10

    Case "B"

    tmp_return = 11

    Case "C"

    tmp_return = 12

    Case "D"

    tmp_return = 13

    Case "E"

    tmp_return = 14

    Case "F"

    tmp_return = 15

    Case "G"

    tmp_return = 16

    Case "H"

    tmp_return = 17

    Case "I"

    tmp_return = 18

    Case "J"

    tmp_return = 19

    Case "K"

    tmp_return = 20

    Case "L"

    tmp_return = 21

    Case "M"

    tmp_return = 22

    Case "N"

    tmp_return = 23

    Case "O"

    tmp_return = 24

    Case "P"

    tmp_return = 25

    Case "Q"

    tmp_return = 26

    Case "R"

    tmp_return = 27

    Case "S"

    tmp_return = 28

    Case "T"

    tmp_return = 29

    Case "U"

    tmp_return = 30

    Case "V"

    tmp_return = 31

    Case "W"

    tmp_return = 32

    Case "X"

    tmp_return = 33

    Case "Y"

    tmp_return = 34

    Case "Z"

    tmp_return = 35

    Case "*"

    tmp_return = 36

    Case "@"

    tmp_return = 37

    Case "#"

    tmp_return = 38

    End Select

    DigitalEquivalent = tmp_return

    End Function

  • Suggest you make this a permanent table (temp for testing)

    create table #equivalent (digit char(1),digitdec int,equiv int,equivdbl int)

    insert into #equivalent values ('0',0,0,0)

    insert into #equivalent values ('1',1,1,2)

    insert into #equivalent values ('2',2,2,4)

    insert into #equivalent values ('3',3,3,6)

    insert into #equivalent values ('4',4,4,8)

    insert into #equivalent values ('5',5,5,1)

    insert into #equivalent values ('6',6,6,3)

    insert into #equivalent values ('7',7,7,5)

    insert into #equivalent values ('8',8,8,7)

    insert into #equivalent values ('9',9,9,9)

    insert into #equivalent values ('A',10,1,2)

    insert into #equivalent values ('B',11,2,4)

    insert into #equivalent values ('C',12,3,6)

    insert into #equivalent values ('D',13,4,8)

    insert into #equivalent values ('E',14,5,10)

    insert into #equivalent values ('F',15,6,3)

    insert into #equivalent values ('G',16,7,5)

    insert into #equivalent values ('H',17,8,7)

    insert into #equivalent values ('I',18,9,9)

    insert into #equivalent values ('J',19,10,11)

    insert into #equivalent values ('K',20,2,4)

    insert into #equivalent values ('L',21,3,6)

    insert into #equivalent values ('M',22,4,8)

    insert into #equivalent values ('N',23,5,10)

    insert into #equivalent values ('O',24,6,12)

    insert into #equivalent values ('P',25,7,5)

    insert into #equivalent values ('Q',26,8,7)

    insert into #equivalent values ('R',27,9,9)

    insert into #equivalent values ('S',28,10,11)

    insert into #equivalent values ('T',29,11,13)

    insert into #equivalent values ('U',30,3,6)

    insert into #equivalent values ('V',31,4,8)

    insert into #equivalent values ('W',32,5,10)

    insert into #equivalent values ('X',33,6,12)

    insert into #equivalent values ('Y',34,7,14)

    insert into #equivalent values ('Z',35,8,7)

    insert into #equivalent values ('*',36,9,9)

    insert into #equivalent values ('@',37,10,11)

    insert into #equivalent values ('#',38,11,13)

    create the following table from input params, either substring 8 chars or 8 individual ints

    create table #digits (digitid int,digit char(1))

    insert into #digits values (1,'4')

    insert into #digits values (2,'A')

    insert into #digits values (3,'*')

    insert into #digits values (4,'4')

    insert into #digits values (5,'Q')

    insert into #digits values (6,'D')

    insert into #digits values (7,'@')

    insert into #digits values (8,'2')

    declare @sum_digits int, @check_value int, @check_digit int

    set @sum_digits = 0

    select @sum_digits = @sum_digits + (case when d.digitid in (2,4,6,8) then equivdbl else equiv end)

    from #digits d inner join #equivalent e on e.digit = d.digit

    set @check_value = @sum_digits / 10

    set @check_digit = @sum_digits - (@check_value * 10)

    if (@check_digit <> 0)

    set @check_digit = ((@check_value + 1) * 10) - @sum_digits

    This assumes that 8 chars input match 1 entry in equivalent table.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • I'm sure that this does what you want, but why not convert the function to a DLL? You could use sp_addextendedproc to create an exenteded stored procedure and then call it as you would any other sp?

    Jeremy

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply