Avoid Select *

A recent discussion on CF-Talk implied cfqueryparam as the culprit in a query problem. It seems, however that the problem only happens when using cfqueryparam in conjuction with "select *".

In the discussion, Ben Nadel pointed out reasons he recommends listing out columns instead of using of "select *". His words:


SQL Server Comments and ColdFusion MX 7

This problem (and solution) comes from my good friend Will Spurgeon of Tech-It-Easy. He has long been using SQL comments when developing for SQL Server. The advantage of this approach is that you can see these comments when reviewing transactions in SQL Server Profiler. It can really help in a lot of trouble-shooting situations.

For example:


Prevent duplicate Inserts

I frequently need to insert a record that relates to tables to each other (or relates one table to external data) and I cannot have a duplicate record. Here is my method for doing this in case it helps anyone and as a convenient reference for myself.

Let's suppose that you have three tables: Users, Classes and Students. Classes has a primary key of ClassID (int). Users has a primary key of UserID (int). Students has a two-column primary key of ClassID and UserID which relate to the corresponding columns in Classes and Users respectively.


Outer Join on Multiple Fields

I ran into a situation where I needed to do an outer join on more than one field (if only SQL supported such a thing). After banging my head against a wall for a bit, I asked the CF-Talk list.

Lo and behold! SQL does support exactly what I want. I was just to dim to try it myself. Mark Gaulin provided the answer:


Join a table to itself in SQL

It recently came to my attention that some developers may not know how to join a table to itself. For them, I write this. Why would you want to do such a thing? Perhaps you have a company that is owned by another company, and you want to get any subsidiaries of a given company.


Extracting Date from SQL Server datetime

I was working on a project yesterday that required me to get a date from a SQL Server datetime field. I needed it to return just the date (not the time), but for the life of me I couldn't figure it out (don't worry about why, just go with me here).

I called my good friend (and local SQL Server answer-man), Will Spurgeon. As usual, he came through for me. I never would have guessed the answer:


BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.