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.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2004-02-02 : 15:58:36
|
| I got me a stored procedure that is set up to take a combination of numeric parameters and text parameters. Problem is that one of the apps to callt his proc is an OCR app, and sometimes it misinterprets the numerics, so I get a percentage or 2.325.1, which meets the criteria in the app for a numeric field (containd 0-9 and periods) but obviously isnt a valid numeric. Since I have already set the datatypes to decimat or int or whatever I ned for my parameters, is there a way to default them if they are not valid numbers, or do I have to recode the SP (and thus all the calling apps) to accept all parameters as text and then convert them to the data type I expect? |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-02-02 : 16:08:02
|
| You can set a default parameter value, but it only uses that value if the parameter is null. Your probably going to have to add some checks at the beginning of the sp to handle this type of error, especially if you want to use a different value if its not valid.- Eric |
 |
|
|
MuadDBA
628 Posts |
Posted - 2004-02-02 : 19:05:28
|
| I am fine with adding checks at the beginning of my SP. Is that possible since I declared my variables as type decimal, int, etc? What do I do? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-03 : 06:10:57
|
| Your OCR app has a weakness/fault....in that all it's doing it pattern matching when it should ALSO be invoking the equivalent of the VB/SQL function ISNUMERIC.You have a fundamental problem to answer here....what 'consistant action' could your stored procedure do with the 'bad data' if it did receive it anyway?....would it not be preferable to have your app, spot the problem and side-track the data to a 'repair queue'...and only have your SP work with 'good data'? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-03 : 06:42:45
|
| Your application is presumably sending character strings and as it is not validating the contents you should either accept those strings and change the data to an acceptable value, give back return code or allow it to error as it is at the moment.You could write a shell SP to validate parameters before calling the SP but it's probably just as easy to change the SP itself to accept character variables then copy them to the correct datatype after validation.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-03 : 09:29:48
|
| Thanks for all the replies. We're going to send it back to the vendor to code the app properly. The problem is that the vendor doesn't seem to see this as a defect in their code, and if we could recode the SP to validate the data without changing its datatype, it would save us a lot of hassles with the vendor. If I have to change it so that everyone is submitting strings, (which require quotes and numerics don't), then all of the other apps that interface with this DB will have to change their code as well. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-03 : 09:52:20
|
| That's the trade off...Get one verdor to change 1 faulty app code....or get loads of other apps changed!!The extra test at the OCR app end....should not be difficult.If you do go the other way....have you a plan/method to deal with the bad data...either to improve/fix it...or to report it somewhere safe to one side? |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-03 : 09:56:15
|
| Yeah, bad data will be set to null and trapped later in our process when it is compared to the loan origination system. That way, someone will be forced to reconcile the bad value the OCR app read and a true numeric value. |
|
|
|
|
|
|
|
|
Validating SP parameters.