Wednesday, August 14, 2013

SQL Updates - One shot

Let's be honest, we've all made that mistake in SQL where we accidentally
forgot to put the right parameters into a where clause, and had to
run down to the dba and beg for a recent backup!

Writing SQL update statements really isn't that difficult,
but sometimes it can be daunting executing them,
depending on how many records you're looking at.

There are a few ways you can make things a little less intimidating.

1 - Work up your where clause in a select statement first, to see how many records would be affected.

For a simple example, let's say Ronald McDonald is in our database as "Ron",
and we want to update to use his full name.

select First, Last, Middle
from dbo.tblEmployees
where Last = 'McDonald'
It should came back with 1 row.
update dbo.tblEmployees
set First = 'Ronald',
    Modify_Date = GETDATE()
where Last = 'McDonald'
Now when you run this, it had better come back with only 1 row affected!

2 - Write your where clause so that if you ran it a second time it would affect 0 rows.

Taking the same example, it's good practice to make sure that
if you run your stored procedure twice, it will not do any updates the second time.

update dbo.tblEmployees
set First = 'Ronald'
    Modify_Date = GETDATE()
where Last = 'McDonald'
    and First = 'Ron'
Now when you run this the first time you'll see 1 row affected.
If you run it a second time, it will show 0 rows affected.
Note that it won't overwrite the Modify date, so you'll preserve the date of the original update.

I call this method a one-shot. It can be very useful if you need to track the history of updates,
and especially useful if you have things like update triggers on your table.

Wednesday, July 17, 2013

Interviews - Be Ready to Code!

Finding a good programmer can often be like trying to find a needle in a haystack.

Sometimes the best ones have no degrees, no certifications and very little varied job experience,
and some of the worst interview candidates have glowing shining resumes that make them look like the next Bill Gates.

The best and most reliable way to filter out the people who actually know what they're doing
versus the people who know how to craft a pretty resume is to throw down some code in the interview.

It might seem taboo to some people, but it can save you massive headaches in the future!

It doesn't even have to be rocket science, just something like:
"Write the code to query table X in database Y, and display the data in a grid."

And for the interviewees?
Show up with a pen and a brain ready to show what you know.
Study ahead of time if you have to!

Saying "Yeah I really don't remember how to do it, I usually just copy past from old code" probably isn't going to cut it.
Your memory doesn't have to be perfect, but if you're as good as your resume shows you should be able to at least get close to the mark.

Sunday, June 9, 2013

You down with OPC? (Other People's Code)

One of the laws of programmers and programming that I believe to be universal is:
Nobody likes anybody else's code

It could just be the leading or trailing spaces, too few comments, too much whitespace or any number of things.
In the end it simply boils down to the fact that it's not YOUR code, but you have to touch it.

As a programmer, there are virtually only two ways to avoid having to work with other people's code:
1 - Quit programming
2 - Die

Even looking back on code you wrote years ago can be like looking at a foreign language as styles and talents grow through the years.

So the only advice I can give here is to try and code NOW with other coders in mind.
Think of it as making a work of art that someone else will unveil some day.

A nicely wrapped present that they will open and get a wonderful surprise and not a box of poop!

Tuesday, May 7, 2013


When you're coding error messages, emails, notices and every other kind of text interface for a user, try to think about how it's going to look 10 years down the road.

If someone forgets to fill in a field, which one looks the best?

Required field: First name
This form requires that you fill in all fields before submitting. Please check the form and review all required fields to make sure they are completed. Required fields are highlighted in RED with an asterisk next to the field.
Cheerio, jolly good day outside isn't it? By the by I noticed that you may have forgotten to fill in one teensy weensy field. Would you mind lending a hand by filling that field out if it's not too much of a bother? Thanks in advance.

As comical as these may seem, I've seen some code that was pretty close to some of those examples.

When in doubt: Keep it simple, and avoid SHOUTING !! with caps lock or exclamation points.