SQL Server trick for matching passed parameter or all (IT related)

Hey...I found out something cool today in computing!

The problem: we want to allow a user to get data for either a specific parameter, or, if the user doesn't pass a parameter, then return all data. For example, we want to return employee data. If the user wants just one employee, they specify his/her employee number. If the user wants records for all employees, they don't specify any employee number.

The old solution: check if the parameter was passed, and if so run a specific query, if not run a general query. This looked something like -
 if parameter has a value
   run complex query
   for specific value
 else if no value (parameter is null)
   run complex query


This is clearly duplication as the line "run complex query" may be 10, 15, 20 lines long. The duplication was necessary because if the parameter was null, then we couldn't do an "equalk"-type exact match i.e. we didn't want to do any match at all in this case. For instance, employee number = 1 returns values, but employee number = NULL does not (if the employee number is null, then we would leave off the "employee number =" bit entirely to return all values). In SQL, NULL is not even a number. It's like the number you have when you don't have a number. No number can ever be "equal" to NULL (however we can test for NULL by saying "IF x IS NULL...")

The new solution: use a CASE statement in the WHERE clause, which means that theoretically the code would look like -
 run complex query
 if parameter has a value then for specific value only or else where the field matches itself


In practice this looks like -
 SELECT EmployeeNumber, EmployeeName, EmployeeOtherData
 FROM EmployeeTable
 WHERE EmployeeNumber = CASE WHEN @parameter IS NULL THEN EmployeeNumber ELSE @parameter END


In the above statement, when the passed parameter (@parameter) is null, we find records where the employee number from the table equals the employee number from the table (always true). If the passed parameter is not null, find records where the table's employee number matches the passed parameter.

This helped me heaps today. Made my code more readable, I didn't have to write as much code, and I had something to write about at lunchtime (and it was code-related). Yay!

A Take On My Job (Computing), Part II

My post of the other day had me thinking - what is it I do in my vocation that I might do differently to the "...thousands, nay, millions of programmers..." around the world? Here's what I think my strengths are, and why I'm doing this kind of thing (IT):

  • I'm a structured type of person - this means that my "code" nearly always has comments, is well laid out, and follows some established standard practices in regards to naming conventions. Unfortunately this adds overhead to my work!

  • I have a pretty good memory - this helps when I start doing something over there, to remember what needed to happen over here. I also remember things I've read even if they're not applicable at the time, and may come back to them when I need to (via Google of course!)

  • I like solving problems - I'm not particularly eloquent or concise in my solutions, but given a computing-type problem, I can usually either come up with an answer (I'm writing this as one of my "answers" is processing in the background, and still going after 17 minutes) or a reason why I can't answer the problem. This is an area that needs improvement, though. I think I too often go for the "brute force" approach of trying something that works, even if it's slow and bulky, rather than use a bit more time to develop a more careful solution.

  • I like solving problems - yes, I already mentioned this, but just to be clear that this is one of the key things I do in my job. It's what I like (a lot) about computing.

  • I have a small amount of creativity - an post titled "The relationship between programming and music" (via Rob Birdwell) makes some interesting points that I (and my wife) can agree with. I don't see what I do as being boring or repetitive, rather it's challenging and creative. I admit that I re-use a lot of the same solutions to similar problems, but often have a chance to re-write them to suit the current situation or my improved ability.

  • I want to continue learning - got an idea? Love to hear it. Got a new tool or gadget? I'll try it. While I have to specialise to some degree, I keep my IT-related reading fairly broad. The one thing I'm not good at, however, is having a wide range of interests. I almost never read stuff about cars, or woodworking, or flower arranging, or [whatever].

The thing is, my strengths may actually be weaknesses (when does structure hinder creativity? when does creativity become too individualistic?)

So, what are your strengths in your job?

Almost Too Hot To Think Today

It's almost too hot to think...around 25 degrees today, but inside pretty stuffy...and it's going to get hotter...have to get me a fan. See for yourself at Yahoo! Weather...

Continuing delirious thoughts in new paragraph...I hope it's clear why I have no writer's insight...see you at the beach...

IT-related Writing, or Lack Thereof

It seems like less and less of my writing here is IT-related. I do admit that I spend most of my time (at work) immersed in programming code, updating databases, reading e-mails in Outlook, performing calculations in Excel, maintaining legacy programs in Access, and surfing the web with MyIE web browser. I suppose often I don't think I've got anything noteworthy or interesting to write about all of that here. I reckon there must be thousands, nay, millions of programmers who do much the same thing.

At the moment I'm interested in consolidating this blog with info about my life. Not that I find my job boring. Just the things that stand out to me are described by the entries I've made so far on this web site, and have yet to make.

I will make the occasional code-centric comment..so watch out!

My Son (Friends and Acquaintances, Part 3)

My son's name is Thomas Samuel Williams V. This name has been passed down from my great-grandfather (who I never knew) to my Grandpa ("Tom", who died when I was in High School), to my Dad ("Tim", aged 58) to me, to Thomas.

Ordinarily we call him T.J. (for Thomas Junior); however as all people who have been small boys know, he gets his full name when he's in trouble. And, as he's two, he occasionally gets in trouble as he tests the boundaries!

I've said this to a lot of people - nothing prepares you for having kids. You can read about it, talk about it, think about it, but the only way to know about it is to have them! My brother Dave once asked why I would have kids. The answer: relationship. I reckon that the Bible tells us that God created us for relationship, not to do work for Him or to share in misery or because He was bored or whatever. You can apply the same to Olivia and I (to the degree that we're humans, not God!). God gave us a natural desire to "multiply". Unfortunately I can't explain it any better than this.

When is it the right time to have kids? I remember that I never wanted to have kids. Olivia and I would talk about it. She was patient and gentle with me. Then one day I felt more of a desire, and could finally see myself with a child (perhaps, I could trust myself being a father). I wonder if this is how it works with others?

T.J. is a beautiful, blue-eyed, curly-haired boy. He's pretty big for his age, which is probably owing to his parentage and the fact he was 11 pound 8 ounces, born. That's big, as Olivia will tell you! He says funny things and sings along to songs and even ads on the radio. He likes the movie "Shrek" and the "Wallace and Gromit" series of animated tales. He plays blocks, and toys, and cars. He has adopted one of Olivia's childhood dolls (we call her Molly the Dolly) and we often play-act with Molly about things that the new baby will do.

It's sad that I can't post pictures, but probably good, because if you've seen my wall at work you'll know that I've got 30 or so photos up. One lady visited my office the other day and asked "How many children do you have?"

My favorite experience of my son to date is going to the farm for his 2nd birthday, just to see the wide-eyed wonder and excitement at, of all things, a train. No, not the myriad animals he had never seen before, but a train painted to look like Thomas the Tank Engine. The great thing is, months on, T.J. still talks about the farm (and the train)!

To my son T.J.: I love you!

Blogs I Visit, and RSS

I used to occasionally go to a list of about 30 of my favourite sites, about once a week, to see if there was new stuff worth catching up on. This gets tiresome (and inconsistent at best) after a while. Gradually I chose my "best of the best" websites, and signed up for e-mail newsletters from the other ones.

Nowadays I don't even have to do that, as I can check on just the updated parts of a site (new articles or entries) when I want for as many sites as I want. This technology is called RSS (I've heard it stands for "Really Simple Syndication"). It's a computer-readable file that can be read by an RSS aggregator (a program that groups all the new items together, under sub-headings). My aggregator is called Bloglines.

Because I subscribe to around 30 websites' RSS, I won't bother to list them all in my "links" section on my blog. I'll just put the ones that are my most-favourite, most-recommended, dog-eared links. Before I remove any of them, though, here's the semi-complete list for your viewing pleasure. The "Feed" represents the RSS (click on it to have a look at what RSS is). Note that the first couple are Christian blogs - the rest are IT-related:

Harry Potter

Wow. No posting for a week. I hope I don't make a habit of that.

I'm starting to admire Darren more and more, even though I've never met nor talked to him. He's posted a whole lot of links on Harry Potter at Harry Potter and the Prisoner of Azkaban - Christian Discussion Links. I discuss the differences between Harry and Tolkien at The Lord Of The Rings.

One other thing to add to the whole "Lord Of The Rings is Christian" debate: I am a bit wary of Christians (including myself) "claiming" or rather labelling certain artists or artistic work as 'Christian'. What if the director of the LOTR movies intentionally changed the story so it didn't reflect the same values as Tolkien had intended? Hmmm...

The Lord Of The Rings

A good summary of online articles and opinions on Christianity and the Lord Of The Rings at The Lord of the Rings and Religion (via Darren).

To give you my two cents: I love these movies, loved the books, and can observe that the Christianity shown is covert rather than overt. One article makes the comment that Tolkien's world operates under Christian principles. I agree with this (looking at the values put forward, the heroes, the nature of choice, evil). I find it hard in my mind to contrast LOTR with Harry Potter (which I am not really interested in) - on the surface, the two might share many similarities: namely, magic and make-believe. However this is not what stands out for me in LOTR.

Why am I not attracted to Harry Potter? Is this me being legalistic? Do I have to see/read Harry Potter to know that I wouldn't like it?

Number of links to "Christian Computing"

Wow! A Google search for "Christian Computing" returned nearly 5,000 hits! That's a lot of reading ahead of me!

Confessions of a Car Salesman

My Dad works in Real Estate. I wonder how closely this article, on Confessions of a Car Salesman mirrors his experience? I reckon it was a fascinating read (all 20-odd pages).

Do we have a service like http://www.edmunds.com/ in Australia (well, I think we have the RACV)?

The relationship between programming and music

There's a link between programming and music (perhaps more between programmers, and musicians). I read it to my wife Olivia, and she agreed. I never thought about it like this...

The relationship between programming and music, via Rob Birdwell

My Wife (Friends and Acquaintances, Part 0)

My lovely wife of nearly 4 years is pretty much the be-all and end-all of acquaintances for me. I know this won't offend any of my other circle of friends and relatives - those that know me know my wife is very, very important!

I met Olivia at a youth camp that I didn't want to be at. I was "killing time" in Easter 1995 with Leigh, when I met her. Our long distance friendship kicked off in the city of Melbourne before returning to its rural roots, Olivia being from the town of Leongatha and I from Cranbourne (an hours drive).

We had ups and downs, and I know with me not being a committed Christian and having some pretty lousy values and attitudes, things didn't go so great. But I do know that God held us together and turned even the things that could have been disastrous, and split us apart, into part of our history and legacy of being together. There (still) is a 5-and-a-bit year age gap between us that can affect relationships when both people are at a young age - imagine the difference in interests and maturity between an 18-year-old and a 23-year-old, and you'll get the picture!

I've always joked that I'm about the right maturity for Olivia. She's very advanced, and I'm slightly backwards. Perfect!

We started living together in 1997, and committed our lives to Jesus in late 1999 before getting married in December 1999. I can't put into words what an exciting time that was for both of us.

I'm sure I'll discuss the intermediate bits as they fill out the overall picture some other time. For now, it's enough to know that Olivia occupies a great part of my thinking and doing, both day and night. I'm complete with her, incomplete without. Olivia, my wife, I love you!

What is Blogging Revisited

Ever since I've got into reading blogs, and considered writing one, I kind of felt like blogging is like standing on a street corner with a megaphone, yelling words up into the air. It seems in some ways to be detached. Chat Rooms (which I have visited but once, for two minutes) are semi-personal; e-mail (when it's not spam) is fairly personal. Phone calls and face-to-face contact maybe round off the list.

Not that the aim is to have someone in your face all the time. I just reckon that it's hard to write as if both no-one and anyone could read what you write at the same time.

I maybe have a different slant on this as according to this online test I'm introverted (Hey, I tend to agree with the test. So does my wife!)

Darren at LivingRoom writes:

Every day we're bombarded by people baring their all. Blogging is just part of the mix of reality TV, celebrity exposés and live news being beamed into our living-rooms round the clock. We have become a voyeuristic society - we clamor for the inside word, to see the real lives of others, yet at the same time are becoming more and more numb to it all.

He sums up with a final question:

In our obsession to see into the lives of others do we somehow run the risk of disengaging from our own?

Things I Read Today, Wednesday November 12th

I had both an uplifting time and a good laugh at some of the stuff I read today. I found three other Christian bloggers (one of who is into IT, one who's an Aussie), and who have some good stuff on their blogs:


Also found some funny stuff:

Hey, I also read "Around The World In 80 Days" today (well most of it today, until I finished it at 2:30 this morning). Cool!

Lastly, I play guitar, and one site I found (via What It's All About) was a Third Day guitar tab site at Guitar-Dude.com. I'm still confused about the legality of tab music sites, as I've seen so many of them shut down or crippled by being forced to remove music. Hmmm...

I should have attached the following warning to the front of this post:
Warning: this is not IT related.

Oh Brother (Friends and Acquaintances Part 2)

I love my brothers.

I have three younger brothers - Simon, David and Nathan - who I think are all the greatest.

Simey married a beautiful Korean woman named Sujin last year, and my wife Olivia, our son T.J. and I had the chance to be there for the monumental event. They're now living in Korea, and keep bugging us to visit again. Sime, we're working on it! One key thing I love about my bro is that he is so faithful to God. This is evidenced in the transformation he has undergone as he serves wherever he's called, and his dedication to his marriage. This may sound like flattery (it isn't), but I wish I could be more like him. Love you bro!

David and I are close in that I reckon we like the same sort of things. He's also a pretty private person, has a tight group of friends and is a hard worker. I often like to see myself in the same light. He's the only one of us not married. Love you Dave!

Nathan is the youngest. He and his wife Nic have a beautiful son called Isaiah who's about 8 months old. We get to spend more time together since he and his family moved back from Newcastle, New South Wales. I identify with him because he's my bro, but also because he's a dad trying to make it the best he can, and has a Christian faith that makes us more than genetic brothers. Love you bro!

Other people I know: Leigh.

I Reckon He Should Blog (or, Friends and Acquaintances Part 1)

I have a mate called Leigh who I think would make the best blogger. His experiences are sufficiently different from most normal people to make for really interesting writing, and the boy can write! C'mon Leigh, what do you reckon?

Leigh, my man in Japan, if you read this, look forward to catching up with you soon.

Comments and RSS

Thanks to a weblog a read in passing, I got some ideas for how to add comments and an RSS feed to my blog. Hopefully now these features will be working!

Thanks to the schmiddy blog, another Great Australian Achiever.

Other Christians In Computing, Part 1

I'm not naive enough to think I'm the only Christian in computing. Here's some of the reasons I don't know of others, though:

  • I've only been reading blogs for around 4 months.

  • There's no mandate for everyone to just blurt out what their deepest personal convictions are on the web. If someone is a Christian and doesn't post it on their blog, they're still a Christian!

  • Sometimes I don't read people's personal stuff.

Found one today - Maxim V. Karpov. I've subscribed!

Things I Read Today, Thursday November 6th

Some of the interesting things I read (and then clicked on) today were:


Blogroll

I will soon list the blogs I read. They're mainly tech-type blogs. I personally use "Bloglines" as my blog reader.

What A Blog Is

What's a blog?

It's kind of like a diary, in reverse chronological order, about what I write. It's a personal web page that can be easily written to.

More applicably for me it's a way to keep in touch, to kick off and contribute to discussions, have an online presence and a way to organise my thoughts. I want to learn more about writing. I value other people's blogs and the ability to perhaps share my own thoughts on a matter.

You probably should read each entry as it it starts and end with quotation marks, and has the disclaimer "Thomas Williams reckons that...". I don't speak for anyone else (my place of employment, my wife, my family, my friends, my community, my country, my denomination, my religion...)

Update: In Australia a poo can be called a "bog" - maybe "blog" is a literary bog?

A Take On My Job (Computing)

There's a story in the Bible where some guys are fishing. They haven't caught anything - which is bad, because these guys fish for a living. It's early in the morning, and they've been out all night. Then, Jesus comes along and tells them to cast their nets out again. To make a long story short, they catch lots of fish (Luke 5:4-11).

I was amazed at this story because at its conclusion Jesus calls them to follow him. One aspect of this tale that jumped out at me was that Jesus could have made them into super fishermen, but He didn't. He had other things in mind. I reckon it's not the fact that these guys left their jobs to follow Jesus that's of merit, it's that they answered His call.

I want to work as a fisherperson as if I'm working for Jesus Himself. I'm excited that His call away, when and if it comes, will be even better than if He made me into a super computer person.

My Blog Title

What is Christian Computing? Why did I title my blog this way?

I see it as my current vocation (computing), done in a way that's honouring to Christ. I'd call my blog 'Christian Living', but that's what I'm doing anyway. My blog is going to be focussed a little more on computing than if it was a complete diary of my life.

That means doing my job with integrity. Not lying. Working for my boss as if I was working for God himself. Not stealing. Giving my time in my job cheerfully.

 

Creative Commons LicenseThis work is licensed under a Creative Commons License. Copyright Thomas & Olivia Williams 2003, 2004, 2005, 2006, 2007, 2008, 2009