SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IsNumeric with NULL and Empty String


IsNumeric with NULL and Empty String

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35654 Visits: 11361
SanDroid (8/4/2011)
"Code to enforce Business Rules should not be in SQL!"

Some days, I would go further:

"Code to enforce Business Rules should not be in SQL!"



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SanDroid
SanDroid
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2416 Visits: 1046
SQLkiwi (8/4/2011)
SanDroid (8/4/2011)
"Code to enforce Business Rules should not be in SQL!"

Some days, I would go further:

"Code to enforce Business Rules should not be in SQL!"

Nice... :-P
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26013 Visits: 12498
SanDroid (8/4/2011)
I have to agree. Every time I look at things like this my brain starts repeating the following mantra:
"Code to enforce Business Rules should not be in SQL!"

Validation of user interfact data is best done by application Functions or Subroutines, not DML. Cool

The trouble with that approach is that when misinterpreted by certain people it leads to stupidities like "uniqueness should be forced by application code, not database constraints" and "since all normalisation except 1NF is about enforcing business rules by schema structure no normalisation beyond 1NF should ever be done". I've heard both of those too often from people who have misunderstood the perfectly simple rule that the application should ensure the data being fed to the database is correct and think that it means that the database should not do it.

The countervailing mantra is that everything, including the databse, should validate every parameter that is fed to it and also ensure that everything it passes to another component is valid. Ideally most of this is done by a decent type system that allows strong domain constraints to be specified. Unfortunately (T-)SQL doesn't (yet) have such a type system, so we get badly thrown together bandaids (like IsNumeric) to patch it up with; TRY_CONVERT is a big improvement, but not perfect - and there may be performance considerations working against a nice clean test delivering 1 for "can convert and is not null", 2 for "can convert and is null", and 0 for "can't covert" (that should include the case where destination type doesn't permit null but the parameter is null, but SQL support for not null types outside of tables is non-existent) since if it delivers a non-zero result the next thing to happen will probably be the conversion and that was probably already done once for the test (a type system catering properly for pairs would of course allow something both efficient and clean, but we don't have even that much of a decent type system).

Tom

Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35654 Visits: 11361
Tom.Thomson (8/4/2011)
...if it delivers a non-zero result the next thing to happen will probably be the conversion and that was probably already done once...

Indeed. Worse, people might write the test and the convert in the same statement in such a way that the convert could be reordered by the optimizer to happen before the test...



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26013 Visits: 12498
SQLkiwi (8/4/2011)
Tom.Thomson (8/4/2011)
...if it delivers a non-zero result the next thing to happen will probably be the conversion and that was probably already done once...

Indeed. Worse, people might write the test and the convert in the same statement in such a way that the convert could be reordered by the optimizer to happen before the test...

Yes indeed. I've seen similar things. I wouldn't have seen them if people didn't tend to think that things are evaluated in the "obvious" order, but this would be another chance for people to make that assumption and get burnt by it.

Tom

Hardy21
Hardy21
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2730 Visits: 1399
Straight forward question...

Thanks
itaigitt
itaigitt
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 157
Pay attention that:
SELECT IsNumeric('') --> 0
Where the empty string is not stored in a parameter - the result will be zero.
kalyani.k478
kalyani.k478
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 97
nice question:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search