Brian Webster - Blog

I’ve used SQL to varying degrees for a number of years. However, one of the features I’ve recently learned about is the CASE statement. For some reason, I missed this powerful feature when I studied SQL.

When would you use the CASE statement? I use it when writing stored procedures when I want to have optional input parameters. Consider this simple stored procedure:

ALTER PROCEDURE [dbo].[GetRooms]
(
@roomID int = 0,
@color varchar = ”,
@type varchar =''
)
AS
BEGIN
SELECT roomID, roomName, roomColor, roomType, roomWidth, roomLength, roomheight
FROM tblRooms
WHERE
roomID = CASE @roomID WHEN 0 THEN roomID ELSE @roomID END
and roomColor = CASE @color WHEN '' THEN roomColor ELSE @color END
and roomType = CASE @type WHEN '' THEN roomType ELSE @type END
ORDER BY roomName
END

With this one Stored Procedure, we have a lot of options for what is returned, depending on what parameters we include in the call.

EXEC GetRooms - Returns all the rooms in the database.
EXEC GetRooms @roomID=3 - Returns the room with ID of 3
EXEC GetRooms @color=’blue’, @type=’kitchen’ - Returns all the kitchens that are blue

With the optional parameters, this one stored procedure can be used in a number of different scenarios. Using this technique, I once replaced about a dozen stored procedures in a database with just one. How cool is that!

On Monday, I received a call from an author of one of our county CMS pages. He was trying to upload a PowerPoint file, and the CMS was not accepting it. He was trying to upload a file that exceeded the 5MB per file limit. The primary reason for this limit is that most of the users of our web pages probably don’t want to wait for that big of file to download.

I suggested a few options that would allow this PowerPoint file to be uploaded. These included:

1. Save it as a PDF file.
2. Resize the photos used in the PowerPoint file
3. Do a Save As…
4. Split the file into part A and part B

About an hour later, I receive an email saying that he had resized the photos, then did a Save As to create a new PowerPoint file. The new file was 856KB, down from 6.5MB. This new file was easily uploaded into the CMS.

By doing a pretty simple process, he drastically reduced the file size, therefore making the file download a lot faster for the people that want to use this file. And the best part is, he did not have to compromise the quality of the presentation. Instead, he used photos that were appropriately sized for the way he was using them.

We typically tell people to do the resizing using Picture Manager. This is a utility that comes bundled with Office 2007. Here is a document that talks about how to use Picture Manager.

Although he was using a the photos in a PowerPoint file, the same principle also applies to web pages. Resize your photos before including them putting them on your web pages. The alternative is to set the display size in your HTML code, however, the browser still needs to download the whole image.

As a side note, we have been looking for a good solution to be able to dynamically resize images at runtime. We’d like to be able to upload the full image into the CMS, and have the CMS resize it when serving up the page. If you are doing this, I’d like to hear about it.

Recently, I added a Google Custom Search to our Program Builder web site. After this was in place for a couple months, the owners of the web site asked why certain pages weren’t showing up in searches.

Doing a little investigation, I discovered much of the information on the individual program pages was also on the summary page listing all the programs. Because of this duplication, Google was eliminating the individual programs from the search results.

The first thing I did to solve this was add a meta tag to tell robots not to index the summary page. I did this by adding this html code:

<meta name=“ROBOTS” content=“NOINDEX” />

While I knew I could allow the summary pages to drop off Google’s results pages without any intervention, it could take a while before Google noticed this change. Therefore, I went into the Google Webmasters Tools, and requested that the summary pages be removed from Google’s results.

The option to do this in under the “Tools” tab in the webmaster tools. You tell Google what URL(s) you want removed, and Google then makes sure you’ve done something to tell it to quit indexing the page(s), such as the above meta tag.

After about a day, the summary page dropped off Google’s search results, and the individual programs started appearing instead of the summary page. Mission accomplished.

More information on Google Webmaster Tools is available in this recording of a session I did for the eXtension Learn series. You can also access my Delicious bookmarks for this session.

Kevin Gamble had an interesting post on this topic, “URLs and the dominance of search.” I’ve known for quite a while that search is extremely import. Web analytics on our web site have been telling us this.

However, Kevin’s post takes this to another level. I’ve never really thought of search terms as being a marketing tactic. It sounds like a great idea. In many cases, it would be a lot easier to remember a search term than it is to remember URL’s.

This will require web authors to work more on the search term(s) that users will use to access their sites. In my opinion, this is something that currently doesn’t get enough attention now, so maybe we can use this as another reason to get content providers to consider how they write.