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

Avoid ISNumeric and dealing with Nullable Columns Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 8:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
I have what is not ideal data that I have to work with.

For example I have a column CD_Limit that contains numeric values, characters such as $, %, etc.

I create a numeric column to store the data so that it is useful for reports, etc.

I used SQL Enlight and I got the warning 'Avoid using ISNUMERIC function as it accepts floating point and monetary number.'

I was also advised to use ISNULL on the CD_Limit column but with the values in the column which I referred to above, I'm not sure what to use as a default or if I can even use that function.

Any suggestions on how I can improve this query would be appreciated.


DECLARE @Date Date;
DECLARE @CD_Limit_ISNull CHAR (1);
SET @CD_Limit_ISNull = ' ';

SET @Date = GetDate();

UPDATE MY_DIMENSION
SET CD_LIMIT_NUMERIC = CASE
WHEN CD_LIMIT = 'NONE' THEN 0
--ELSE ISNULL(CD_LIMIT, 0)
WHEN ISNUMERIC(MY_DIMENSION.CD_LIMIT) = 0 THEN 0
ELSE ISNULL(CD_LIMIT, @CD_Limit_ISNull)
END
WHERE CD_STG_SOURCE_SYSTEM = 'RDB_DWH'
AND CD_DW_INSERT_DATE > DATEADD(DAY, -7,@Date);



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1531626
Posted Thursday, January 16, 2014 10:19 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
I get the following warning when using SQL Enlight:

 Avoid using ISNUMERIC function as it accepts floating point and monetary number.



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1531686
Posted Thursday, January 16, 2014 10:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
I'm not overly familiar with enlight, but your approach seems reasonable. The big deal with that is the scientific float items (1.03E-12) which may/may not fit into the target. You may want to wrap for that with an upper/lower bound for the values.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1531701
Posted Thursday, January 16, 2014 11:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
Thanks Kraig.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1531720
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse