I have a field that stores reference numbers. The numbers may be up

to 12 digits long and are sometimes suffixed with a letter.

eg/

14242

123345

4223344

123343

122134432A

123345B

The reference number field therefore has to be of type VARCHAR

My problem is that I need to write a stored proc to return the next

number in a numerically ordered sequence.

ie/

given an input para of 123343

the sproc should return 123345

given 123345

the sproc should return 123345B

but this obviously sorts on VARCHAR and returns the wrong result

Any ideas???