Possible with Stored Proc? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Possible with Stored Proc?

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-15 : 13:47:14

Hello..

I have been given a function (written in VB, compiled as a DLL) that adds a Check Digit to a number. Rather than having to instantiate the DLL (I guess as some sort of COM object), I'd rather be able to handle all of the function within a Stored Proc.

The function has three arrays that it needs to search through. I think that Stored Procs can't handle arrays....but I read something about using CSVs instead. Would this be a good way to handle this situation?

The function also has some sub-functions....so the stored proc would also have to contain these sub-functions. Is that feasible for stored procs?

thanks
- dhw

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-15 : 14:33:53

Probably what I would do is put those arrays into tables and do what you need to do from there.

If you gave us more detailed information we cna give you a better idea of how it needs to work.

Michael

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-15 : 19:06:35

Thanks Michael for the offer of help. When you say put the arrays into tables....do you mean create a table which has a column for each array element??

Here is the basic function:

Dim F(,) As Integer = {{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, {1, 5, 7, 6, 2, 8, 3, 0, 9, 4}, {5, 8, 0, 3, 7, 9, 6, 1, 4, 2}, {8, 9, 1, 6, 0, 4, 3, 5, 2, 7}, {9, 4, 5, 3, 1, 2, 6, 8, 7, 0}, {4, 2, 8, 6, 5, 7, 3, 9, 0, 1}, {2, 7, 9, 3, 8, 0, 6, 4, 1, 5}, {7, 0, 4, 6, 9, 1, 3, 2, 5, 8}}

Dim Op(,) As Integer = {{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, {1, 2, 3, 4, 0, 6, 7, 8, 9, 5}, {2, 3, 4, 0, 1, 7, 8, 9, 5, 6}, {3, 4, 0, 1, 2, 8, 9, 5, 6, 7}, {4, 0, 1, 2, 3, 9, 5, 6, 7, 8}, {5, 9, 8, 7, 6, 0, 4, 3, 2, 1}, {6, 5, 9, 8, 7, 1, 0, 4, 3, 2}, {7, 6, 5, 9, 8, 2, 1, 0, 4, 3}, {8, 7, 6, 5, 9, 3, 2, 1, 0, 4}, {9, 8, 7, 6, 5, 4, 3, 2, 1, 0}}

Dim Inv() As Integer = {0, 4, 3, 2, 1, 5, 6, 7, 8, 9}

Function MakeCheck(ByVal INVAL As String) As String
Dim InputValue As String = "x" + ReverseString(INVAL)
Dim check As Integer = 0
Dim v As Integer
For i As Integer = 1 To InputValue.Length - 1
v = Val(InputValue.Chars(i))
check = Op(check, F((i Mod 8), v))
Next
Return (INVAL + Convert.ToString(Inv(check)))
End Function

Function ReverseString(ByVal sText As String) As String
Dim lenText As Long, lPos As Long
If Len(sText) = 0 Then Exit Function
lenText = Len(sText)
ReverseString = Space(lenText)
For lPos = lenText To 1 Step -1
Mid$(ReverseString, lenText - lPos + 1, 1) = Mid$(sText, lPos, 1)
Next lPos
End Function

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-15 : 19:47:52

this should be really easy in T-SQL ... but what is Op() ? is that a function?

- Jeff

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-15 : 23:09:51

OP() is another array. There are three arrays....F, OP and Inv. The two functions are MakeCheck(), which calls the ReverseString() function.

Do you really think that converting this to T-Sql to run in a stored proc will be easy? I have tried numerous things...all of which don't work.

Thanks for any help!
- dhw

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-15 : 23:11:27

I've done mod 10 checkdigits in SQL, it's kinda ugly looking, but not too bad.

What's the algorithm for this check digit? If you post it we can probably put something together.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-16 : 01:07:09

Great.

When you ask, "What's the algorithm"...do you mean beyond the function or source code I posted earlier today? I didn't write it...but I think it is a Mod 8 check digit. let me know if you need something beyond the function....I can see if the developer that gave it to me can provide more info.

thanks!
... dw

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-16 : 08:09:45

Basically, what are the steps used to derive the checkdigit. The function you have performs lookups into an array. I've done something similar, but I'd prefer to know the entire process to be sure I'm doing it right (explained in a non-code way)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-16 : 09:41:14

Oops, I should've seen that OPS() is an array -- my bad ...

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-16 : 10:19:39

you could do something like this:


create function ReturnFromArray(@Array as varchar(200), @Dimension1 as int, @Dimension2 as int, @Dim2Size as int)
returns int
as
begin
return convert(int, substring(@array, @dimension1 * @Dim2Size + @Dimension2 + 1,1))
end
go

create function makeCheck(@Input as varchar(100)) returns varchar(100)
as
begin
declare @F char(80)
declare @OP char(100)
declare @INV char(10)

-- i might not have typed these in right -- double check:
set @F= '01234567891576283094580379614289160435279453126870428657390127938064157046913258'
set @OP='012345678912340678952340178956340128956740123956785986043216598710432765982104387659321049876543210'
set @INV='0432156789'

declare @v int;
declare @i int;
declare @check int;


set @Input = reverse(@Input) + 'x'
set @check = 0
set @i=1
while (@i < len(@input))
  begin
  set @v = convert(int, substring(@Input,@i,1))
  set @check = dbo.ReturnFromArray(@OP, @check, dbo.ReturnFromArray(@F, @I%8, @v, 10), 10)
  set @i=@i+1
  end

return @Input + convert(varchar(1), substring(@Inv, @check+1,1))
end
go

select dbo.MakeCheck('1234923')

go
drop function ReturnFromArray
drop function MakeCheck

this is not checked versus your code, but it should work. it just does multidimensional variables the old fashioned way, as 1 long string. This is easy because all of your values are 1 character long; I didn't even need to use tables as I thought I might -- but you could if you wished.

The function ReturnFromArray() is the key, which takes a multidimensional string in a "flat" format as an argument, as well as the 1st and 2nd dimensions to return (currently, this is for 0 based arrays, you may need to change this). The 4th argument is how big the 2nd dimension is, since that tells the function how to calculate the position of the string to return.

thus, you would convert

{{1,2,3},{4,5,6}}

as

'123456'

and to return element (1,1) (which should be 5) you would call:

dbo.ReturnFromArray('123456',0,1,3)

since the 2nd dimension has a size of 3.

Single dimensional arrays are returned just using SUBSTRING(@S,@n+1,1), where @S is the string of values and @n is the index.

anyway, this is not totally tested versus your algorithm and I probably messed up the typing of @F and @OPS and all that which will certainly screw things up, but hopefully it gives some ideas!

- Jeff

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-16 : 11:23:39

Rob - okay..you want to see something more like pseudo-code. Got it. I'll see about getting something for you to see in a little while.

Jeff - Wow, thanks for the help. that was alot of typing for you, I appreciate the time and effort. I'll try it and see if I can get the same results as the function.

Thanks everyone.
- dhw

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-16 : 13:16:03

Rob....the developer that gave me this function is out today. So, I will try and translate it as best I can. It might take me a while.

Jeff - I created the two functions and ran it to compare the results to the VB.Net code. They don't come out the same...but I think that the reason is...and maybe I am wrong...but in the Vb.Net code, all of the references to the array elements are using a Zero-Based index and I am guessing that Sql functions might use a an index that starts with '1'. Does that make sense...or am I wrong?

thanks
- dhw

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-16 : 13:21:40

In the code I gave, the arrays should be zero-based. ... re-read my post and check all the comments. As I said, check out my defintions of @OP, @F and @INV -- i wouldn't be surprised if I didn't type them correctly.

the function(s) I gave may not be perfect (and probably aren't), but it should show you some ideas.

- Jeff

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-16 : 13:54:34

Jeff....

I see that you are right now. I ran some more tests....some input values match (between the vb function and the sql function) and some do not. Your definitions of the arrays were correct except for 1 number and I had already fixed that one.

I will need to spend some time tracing through some numbers that match and a few that don't to figure out where the discrepancy lies.

Again, thanks alot. This will work and save me lots of time.

- dhw

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-16 : 15:36:02

Jeff, et al...

Just to let you know, I got this working now. And, the results match the original VB.Net function. Just a couple of minor tweaks.

Thanks again for all of your help.

... dhw

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-16 : 15:44:46

no problem, glad it worked, it was a fun challenge. Let me know what the developer thinks of my T-SQL version of his/her code !

- Jeff

- Jeff

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-03-17 : 02:47:59

Jeff...The developer thought that it was great.

Thanks again!
... dw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources