Simplify Simplify Simplify

Here is the code as found ::
private string FormatTime(object DateTimeValue)
string result = " ";
if (DateTimeValue != null)
DateTime dt = DateTime.Parse(DateTimeValue.ToString());
if (dt.Hour < 10)
result = FILLER_NUMBER + dt.Hour.ToString() + ":";
result = dt.Hour.ToString() + ":";

if (dt.Minute < 10)
result += (FILLER_NUMBER + dt.Minute.ToString() + ":");
result += (dt.Minute.ToString() + ":");

if (dt.Second < 10)
result += (FILLER_NUMBER + dt.Second.ToString());
result += dt.Second.ToString();

return result;

Here is the refactored version::

return DateTime.Parse(DateTimeValue.ToString()).ToString("HH:mm:ss");


The wheels of the object go round and round

Notice that if dsSTData were null you will allocate it, but not use it to look up the ThingyMin :)

public int ThingyMin
int _ThingyMin = 0;
if (dsSTData == null)
this.dsSTData = ONAME.ProdName.Common.Business.CONAMESystemDataList.STs;
foreach (System.Data.DataRow row in sSTData.Tables[this.DEFAULT_TABLE].Rows)
if (Convert.ToInt32(row[0]) == this._STID)
_ThingyMin = int.Parse(row[OllCommon.COL_THINGY_MIN].ToString());
return _ThingyMin;


How to ruin a set of data

From my bud. Not strictly SQL but close enough. He would like to know:

I'm not asking a lot. I just would rather see
"if (this.Order.IsPacked)"

instead of
"if (
this._DataSet.Tables[OrderCommon.TBL_ORDER].Rows[OrderCommon.DEFAULT_ROW][OrderCommon.COL_IS_PACKED] == DBConst.DBNull)"


Built In Bugs :: VarChar

Alexander Kuznetsov pointed this one out on his blog. He goes on to a longer explanation and how to test for the issue in general (see link). SS

In Transact SQL you can assign a 10-character value to a VARCHAR(5) variable, as follows:

DECLARE @c VARCHAR(5); SET @c='1234567890'; PRINT @c


The value will be silently truncated without raising an error. Because of this behavior it may be very easy to make mistakes.


Please use comments ...

Many management types continually rant about leaving comments in the code so that the next person will be able to figure out what is going on. Sometimes it just proves something else ...

'* Comments: IMPORTANT: The global constants are being temporarily prefaced with "" until
'* all the reports can be integrated with calls to the delegate methods. Afterwards,
'* a global search/replace of "" with "" should rename the constants to coding
'* standards compliancy.


This is going to hurt a little

Imaging a 5100 line stored procedure that is doing a conversion of some data. After hundreds of site updates, you get called in to find a wee little bug that seems to have no consistent behavior but it is definitely in the conversion. Then some where in that 5100 lines of code you find these snippets - several hundreds of lines apart but interacting just as viciously:

CREATE TABLE #Duplicates
,i_id INT
, and other things

..eventually some insert code fills this temp table. Later we find this new table:

, and other things

...which also gets properly loaded. Eventually final results are loaded into yet another temporary table. I will spare you the details and just point out the join here;
SELECT some stuff
FROM #Itemslist il
JOIN #Duplicates d ON il.RecID = d.RecID
JOIN etcetera

Fortunately the duplicate table was very small which is the only thing that contained the error that propagated from this.


Where else does one equal zero?

From more of the old code from Hyberia: We figure that this is the lazy man's (person's) method to making insert tables without all that messy typing.

SELECT rlk.*, rlk.i_id as i_id2 --CREATE #RSLAG table
INTO #RSLAG --note.. the i_id2 column was added to
FROM RSLAG rlk --cause the original i_id column to be
WHERE 1 = 0 --created without the IDENTITY property

SELECT rlk.* --CREATE #RSLAG_total table
WHERE 1 = 0


Query to NoWhere

In a three hundred line stored procedure (not bad) one developer noted that the performance issue came down to the corrolated sub-query. Upon inspection, this left-joined monster was not even referenced by any output column, where clause or reference.

What a FIND!

WHERE toc.x = 400
GROUP BY toc.y
) mt ON tmt.ord = mt.ord
AND tmt.starts = mt.starts
ORDER BY more_stuff


Code Blurg

When you are not really sure what you want, you can try it in more than one place! At the worst you get an additional line of code for your metrics.

insert #DeleteSpots

Select os.Linked_ID, os.BID

from #olspot ols

join dbo.ORDERED os on os.bid = ols.bid

AND os.B_Position IN(0,1,3)

where os.Linked_ID is not null

and os.B_Position = 3


Resolution notes are required for all fixes

This came from our crazed supervisor today ...

From now on, please make all resolution notes in Haiku form. By way of example:
Try it again please.
Whatever the problem was;
Works on my machine.


Why have all the commission rates gone?

I liked this one. This is what happens when you start cutting and pasting code without looking at the end result real close.

In this situation, the developer was removing a variable that had a default value of zero. Guess they figured that the safest thing to do was to replace it with that number. There were a couple of places in this 460 line procedure to change. They must have checked the top half which was just fine, but the original designer had basically copied the first 200 lines and duplicated them in the second half - and he did not use the variable the same way. The maintenance developer did not check this second half which ended up as below.

round(convert(money,sum(r.rate) * 0),2)
,round(convert(money,sum((r.rate * 0) * isnull(r.ag_com_pct,0))),2)


The French know the secret to destroying database performance!

I saw this on GOOGLE VIDEOS and thought it was the funniest SQL performance technical explanation I had ever seen. His name is Stephane Faroult at RoughSea Ltd. It is also nice in that he turned the problem on its head and looked at the performance problem backwards. How to make it worse.

Who would have believed it

I was reading Aristotle and thinking how I could create a software development paradigm based on his nicomachean ethics. Then I ran into an LA Times blog about these guys and their radio program. I think it terribly interesting to see where this show airs - plus its a lot of fun to listen to. All the past shows are available for download.
"This is not a lecture or a college course, it's philosophy in action! Philosophy Talk is a fun opportunity to explore issues of importance to your audience in a thoughtful, friendly fashion, where thinking is encouraged".


A Shortage of ees

Well, now ush has really done it. Apparently gloal warming has een sighted in unexpected areas. Everody is talking aout the mysterious disappearance of ees. There are less and less of them and the ig question is why? I dont know why people get all worked up aout this kind of thing. Just get a new keyoard.



Nate tells me that he used to go to an origami class before the school folded.



From another conversation ...

"It reminds me of the handyman who caught a fish shaped like the letter L.

He, as it turns out, metacarple".


One of the 1.3 million returned results from a google search of PINK LEMONADE will get you this ...


In the old days, a 20MB harddrives setup as a fileshare would get loaded up with junk after a couple of months. It would be horribly disorganized where you couldn't find anything. No one would clean it out because they were afraid they would lose something important. Someone would finally just get another drive. Then the same thing would happen again.

Now its just more people messing it up. More harddrives. The internet should have some standards set that we all have to adhere to before it fills up with porn and puke.

License & copyright

Creative Commons License
StinkySQL weblog and any related pages, including the weblog's archives are licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.