Avoid ISNumeric and dealing with Nullable Columns

Question

Post reply

Avoid ISNumeric and dealing with Nullable Columns

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 16, 2014 at 8:56 am

#281250

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/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

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/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

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/