Tag: syndicated

Edit: This was a poor example of using the divide by zero handling. I was trying to alter something I’d done in the past and this didn’t work well. I will rewrite this soon with a better example.

I have a few posts on computer columns, the basics of computed columns, using CASE in a computed column, and UDFs in computed columns, but there was another use that someone pointed out to me recently: catching divide by zero errors.

Suppose you have a column that is determining a percentage of profit for some sales. I’ll create a table and include some values:

CREATE TABLE MySales
( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost)/ cost
) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99
INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99
INSERT MySales SELECT 1, 'Soda', 0.25, .99

If I look at the values in the table, I get back these items:

/*------------------------
SELECT Product
, Cost
, Profit
 FROM MySales
------------------------*/
Product              Cost                                    Profit
-------------------- --------------------------------------- ---------------------------------------
Bike                 150.4500                                0.202991026919242
Shoes                23.5500                                 0.952866242038216
Soda                 0.2500                                  2.960000000000000

Things work great, and everything seems to be fine. However, what if we get some free products that we can sell, say something we made out of scraps, or were given to us as a gift with essentially no cost?

INSERT MySales SELECT 1, 'Keychain', 0, .75

It works. However if I select data:

SELECT Product
, Cost
, Profit
 FROM MySales

I get this:

Product              Cost                                    Profit
-------------------- --------------------------------------- ---------------------------------------
Bike                 150.4500                                0.202991026919242
Shoes                23.5500                                 0.952866242038216
Soda                 0.2500                                  2.960000000000000
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

The computation occurs on query, and it doesn’t work well.

However we can fix this with a function in our computed column. There are a couple choices here. I can use ISNULL and a CASE to build a formula, I could use COALESCE, or I could use NULLIF.

NULLIF returns null if two values are equal. If a NULL is acceptable in my table, I could use that. I could end up with:

CREATE TABLE MySales
( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost) / NULLIF(cost ,0) ) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99
INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99
INSERT MySales SELECT 1, 'Soda', 0.25, .99
INSERT MySales SELECT 1, 'Keychain', 0, .75
GO SELECT Product
, Profit
 FROM MySales

and get back:

Product              Profit

——————– —————————————

Bike                 0.202991026919242

Shoes                0.952866242038216

Soda                 2.960000000000000

Keychain             NULL

NULL is a good marker here, but your application needs to handle this and let the user know there is an issue.

You could use COALESCE, which returns the first non-null value. I could see any of these as being a valid formula:

, profit AS (price - cost) / COALESCE(cost ,0) 

which uses “0” profit margin as a marker, or even something like:

, profit AS (price - cost) / COALESCE(cost , 999)

which uses 999. Lots of times we’ve used a large, nonsense number as a marker that lets someone know there is a strange value here.

If we converted this to a varchar, it’s possible that we could even use words in there, but I wouldn’t recommend that as downstream uses of this column might involve other calculations.

That’s a short look at how a computed column can solve an easy, common issue: divide by zero.

Credit to Atif Shehzad, whose article I found while researching this.

Have you ever needed to do a quick time calculations of the amount of hours/minutes/seconds that have passed? Suppose you needed to get the total number of minutes that have passed for a total time of ‘2:24’.

There are some easy ways to do this, and the normal calculation that you might make is to multiple hours by 60 and then add minutes, so something like:

DECLARE @t TIME, @n INT SELECT @t = '2:24' SELECT @n = DATEPART( hh, @t) * 60
          + DATEPART(mi, @t) SELECT @n

That returns 144, which is the correct value (60 * 2 = 120, adding 24). However there’s an easier, and cleaner, way.

SELECT DATEDIFF(mi, 0, @t)

You can let SQL Server do the math, grabbing the DATEDIFF function and using 0 as a starting point.

Number of seconds in a day?

DECLARE @t TIME, @n INT, @d DATETIME SELECT @t = '11:59:59PM' SELECT DATEDIFF(ss, 0, @t) + 1

I needed to return an identity value recently from an insert for use in another piece of code. For a client front end, you can easily encapsulate your insert in a stored procedure and then SELECT scope_identity() to get the last identity. However there’s an easier way: the output clause.

The OUTPUT clause is a clause that goes in your INSERT statement and allows you access to the INSERTED table, just like a trigger (also the DELETED table.

A short example below, where data is being added by the server in the state of an identity and a default. I am returning them with the OUTPUT clause.

CREATE TABLE mytesttable
( MyID INT IDENTITY , mychar VARCHAR(20) , mydate DATE DEFAULT GETDATE() ) GO DECLARE @mytable TABLE ( i INT, d DATE); INSERT dbo.mytesttable (mychar) OUTPUT INSERTED.myid, INSERTED.mydate INTO @mytable
VALUES ( 'First Row') SELECT i, d FROM @mytable

There are any number of ways to use this data, especially in terms of logging or inserts into another table. It should be cleaner code, but it doesn’t mean that you should be running inserts from the client without stored procedures, or at least without explicit parameters. Make sure you still use those.