Dynamic SQL? Not if I can help it

I’m eschewing my traditional Django/Python banter, at least for a little while, in favor of some of the full-time work I’ve been doing now using (gasp) .NET, C#, and SQL Server.

We’ve got a table that has a foreign key that’s not explicitly stated as a foreign key; rather, it’s a parent entity ID, with a partner column for the parent entity’s name. It allows a relationship to any other table’s primary key, as long as that table’s name is in the parent entity name field. The idea is that a row in this table (an email marketing campaign, for instance) can be tied to any table along a certain marketing categorization level’s table (top-level table for company, mid-level table for a promotion, bottom-level table for a campaign, etc.) if it’s applicable for a certain level of the marketing categorization hierarchy. Sorry, I hate using big words.

The puzzle lies in running a query when given an item that references one of those bottom-level tables, and the goal is to get a list of those email marketing campaigns. At first, I thought perhaps a dynamic SQL statement was in order, so I could do a SELECT against a variable that contained a table’s name. That’s a pretty bad idea, as that can introduce vulnerabilities for SQL injection attacks.

This blog knocked it right out of the park for me, because I was pondering putting in a case statement in my joins. That’s invalid SQL code, however. I used an left outer join instead against a view we had created before-hand that aggregated a hierarchy per row based on the bottom-level table. That way, depending on the value that was in parent_entity_name, it would do a left outer join against the appropriate column using or clauses. Worked like a charm. If you want to weed out duplicates, throw a distinct() in the top of your select statement around the primary key of the table you’re searching for.

Rdio invites, anybody?

I’ve got a few invites to the popular-yet-secretive music service Rdio, for those interested. The first three people to leave comments regarding what they plan on listening to with Rdio will get one!

Don’t worry… you won’t be judged on your responses. Just your response time.

The housing adventure

I’m going to tell you a bit of a story. It’s probably going to be longer than I usually dare to type, but there’s a lot to it, and it probably would ease my (current) anxiety to tell it. So here it is.

For some time now, Laura and I have been looking to move back to the Kansas City area to be closer to family and to be closer to an area with which we’re more familiar. So we set our sights on the Liberty area. It’s a great city that’s still growing a great deal, and it seems to have more houses with character than a lot of the run-of-the-mill suburban villages around the area.

For a month before actually looking in person, we pulled a bunch of listings from online. We knew our price range, how many bedrooms/bathrooms we wanted, and that we wanted a big-ish backyard with a treed lot. So we went to work and found a slew of houses. During this process we actually narrowed down our price range, too. We found a handful that we wanted to look at in person, and we met with a mortgage consultant who helped us figure out our home loan paperwork, and he introduced us to a buyer’s agent at RE/MAX who pulled our listings and showed us around some other places we may not have previously seen.

It was at this point we found a house we really liked. I read various websites that tell house shoppers not to fall in love with a house. I tried keeping that in mind, but it was hard. This house was a lot of what we were looking for. It was unique, very affordable, had a rather huge backyard, and trees everywhere. It had a privacy fence, new fixtures all over the house, a wet bar in the basement, and more. We were interested in putting out an offer for the house, but alas: we were too far out. Nobody would want to take the house off the market for four months while we finished with school and moved back to the area. Of course, the night I found that out, I also discovered the job I thought had been waiting for me back in the area was no longer there. We were in a rut.

We held off on looking for houses for a few weeks. More than anything, that was probably a smart move. We cleared our minds of the huge letdown that happened earlier, I hit the job market and had a few opportunities lined up, and then once the dust had settled, we started looking again. We found a great deal more this time than we had before.

We prepared a short list and met with our buyer’s agent again once I was sure I had a job lined up again, and we looked around again. What we found was a disappointment. We never really found anything we liked, and we went home discouraged yet again. We went to bed, woke up in the morning, grabbed some coffee, and perused the web for more houses. I found one that looked quite nice, but we were leaving for Springfield again that afternoon and couldn’t visit it unless we shuffled around our itinerary. So we did.

We got ahold of our buyer’s agent again (who was gracious enough to meet with us and show us the house on a very short notice) and browsed the house. It was a little small for the asking price, but it had a lot of nice extras. There was a sun room, a detached garage (in addition to the attached one), a gorgeous remodeled inside to the house, new kitchen stuff… it was cool. And we wanted it. This time we knew we’d be inside our time frame for closing (generally 60 days). So we met that evening and signed an agreement — all the paperwork needed to enter into a contract — and named a price (about $15k under). And then we waited.

Less than 24 hours later we received a call from our agent (we were back in Springfield at this point) saying they had a counter offer for us, which was about $10k over ours. On top of that, they wanted a closing date that was 20 days ahead of ours, which was undoable for us (as we were both still finishing up school at that time). We countered again at $5k above our original offer with our original closing date again. And we waited again. This is some of the most nerve-wracking waiting I’ve ever done. I kept worrying that the sellers would outright reject us. But the sellers came back with a counteroffer, again meeting us in the middle, so we settled for that reasonable price, and into a contract we went!

Then came the house inspection period. We contacted Ladco Property Inspections, Inc. — who were great, by the way — to give the house a lookover and make sure everything was in order, and to let us know if it wasn’t. We found that the roof needed a redo, as it was pretty old and in bad shape. We submitted our request to the sellers, and then waited again. To our surprise, they accepted our condition regarding the new roof, and they allowed us to keep pretty much any furniture that was left in the house. Thus in addition to the couches and barstools, we got a day bed, an armoire, even a few televisions. Bonus!

Things slowed down a fair amount after the resolutions were signed. Laura and I finished up school and work in Springfield (I moved back to the Kansas City area early to start my new full-time job), and the loan got approved (pending my pay stub). After working at my great new job in KC, we got that paystub a week before closing, which helped to seal the deal that next Thursday at closing (and there wasn’t as much paperwork as people have told us, either). That weekend we rented a U-HAUL, got almost all of our stuff out of Springfield and moved it into our new house.

We’ve been in it for just over a week now, and we both love it. We had a bunch of family and friends over yesterday as sort of an open house / graduation party for Laura, and from what I could tell, everybody else likes it, too! It’s been a little too muggy to sit out on the porch, but eventually we’re just gonna spend whole evenings out there… the view from the backyard is gorgeous. We’re right up against a creek with great big, old trees, there are birds everywhere, and you can’t see a single house behind yours. At least not until winter. Our neighbors are great (one even brought us a 6-pack of Boulevard Pale Ale last night as a welcoming gift), the location is far enough away from the bustle of Liberty but still only a few minutes drive away from everywhere we would want to go.

This is the start of something great.