Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

IsNumeric with NULL and Empty String Expand / Collapse
Author
Message
Posted Thursday, August 4, 2011 12:34 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 9,928, Visits: 11,204
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1154512
Posted Thursday, August 4, 2011 12:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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...
Post #1154513
Posted Thursday, August 4, 2011 2:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 7,923, Visits: 9,649
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.

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
Post #1154614
Posted Thursday, August 4, 2011 2:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 9,928, Visits: 11,204
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1154621
Posted Thursday, August 4, 2011 5:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 7,923, Visits: 9,649
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
Post #1154702
Posted Thursday, August 4, 2011 11:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
Straight forward question...

Thanks
Post #1154752
Posted Monday, August 8, 2011 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:16 AM
Points: 2, Visits: 152
Pay attention that:
SELECT IsNumeric('') --> 0
Where the empty string is not stored in a parameter - the result will be zero.
Post #1156031
Posted Thursday, January 24, 2013 9:01 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:45 AM
Points: 616, Visits: 97
nice question
Post #1411447
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse