SQL Computer Column Formula

Question

A SQL Table (Trades) has three fields AvgProfit, MinProfit and Hold - Hold is a Computed Colum...

If AvgProfit < MinProfit, Hold = 'Hold' and if AvgProfit > MinProfit, Hold = 'Trade' The Computed Col formula for Hold is as follows.

 SQL Table Trades
 AvgProfit varchar(35)
 MinProfit varchar(35)
 Hold varchar(35)


 (case when CONVERT([decimal](8,4),isnull([AvgProfit],(0)),(0))>=CONVERT([decimal](8,4),isnull([MinProfit],(0)),(0)) then 'Trade' else 'Hold' end)

PROBLEM: Updates cause the AvgProfit to be empty at times and this results in an error when the table references the Hold formula

'Error Converting varchar to numeric'

How do I add IS NULL or EMPTY to the above formula, the ISNULL does not catch AvgProfit = '' ??

Answer 1

varchar columns should be used for VARiable sized CHARacter columns, not for numeric data.

In other words, they shouldn't be empty (other than NULL, of course, but you've taken care of that).

If you cannot fix that little oversight for some reason, you can make your queries even more complex and slower :-) by doing something like:

select case when xyz = '' then 0 when isnull(xyz) then 0 else xyz end ...

In other words, check for both NULL and empty varchar values.

Myself,I'd fix the schema since it'll be better for you in the long term. I'm merely offering the other solution on the chance that you're not able to do that.

Answer 2

Consider typing your database with decimal or numeric columns as paxdiablo has suggested. Is there a reason why those columns are set as varchar?

Alternatively if you have to keep your varchar columns, try ISNUMERIC(), http://msdn.microsoft.com/en-us/library/ms186272.aspx. It works with all common SQL numeric types. So you query might become:

ISNUMERIC can be used like this:

select case when ISNUMERIC('123') = 1 then 1 else 0 end ' returns 1
select case when ISNUMERIC('xyz') = 1 then 1 else 0 end ' returns 0
select case when ISNUMERIC(null) = 1 then 1 else 0 end ' returns 0

So your query might become:

case when isnumeric(AvgProfit) = 1 THEN case([AvgProfit] as decimal) else 0 END

Or something similar.