An Ingenious Banner Rotation System, Part 2By Bill Graziano
In Part 1 we looked at the features for the banner rotation system. In this part we'll examine the administration page!
Updating an Ad
My administration page is called
BANNER_Admin.asp. The page is a relatively basic Add, Edit, List
page that calls a single stored procedure for all it's database work. It has almost no error checking since I'm
the only user for it. It only has one quirk that really needs explaining:
That line of code converts all single quotes in an HTML string to two single quotes. SQL Server uses single quotes to enclose text strings. When you are inserting a record into SQL Server that contains a single quote (or apostrophe) inside a string, you need to replace it with two single quotes. You can read more about that at http://www.sqlteam.com/item.asp?ItemID=293 or at http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=27.
This ASP page calls a stored procedure called
spBANNERPostAd01. A quick word on my naming
conventions. All stored procedures start with
sp. This is a throwback to the days when SQL Server
didn't break objects up into those nice handly folders. I always have a word or abbreviation to identity which
subsystem this object belongs to. All these objects use
BANNER to identify them. Next is the verb
Post to describe what I'm doing and then
Ad which describes what I'm posting.
I attach a two digit version number to each stored procedure. This gets updated whenever the parameters I pass
to the stored procedure change. For example, if I add another parameter to a stored procedure I'll create
a copy with an
02 at the end. I always leave the
01 in place. Sometimes I can just
01 to call the
02 and pass it the new information. Most times I just modify
01 to return a nice error condition that my code can handle gracefully if I didn't manage to
change it. This also makes it easier to search through code and find what needs to be updated.
spBANNERPostAd01 takes each column as a parameter. If the
AdId = 0, it will do an
insert, otherwise it will update the passed in record. At the end it updates the
is where the cool SQL Server stuff happens. This part of the stored procedure looks like this:
UDPDATE statement clears the
WeightScaled field if the ad is not assigned a
weight. This will prevent it from being selected. You could probably make that part of the first
statement if you really wanted to. I chose not to for simplicity sake. It just makes the code easier to read.
UPDATE statement assigns the scaled weight. This is the value that I'm going to use to
randomly select an ad. It looks a little odd with two equal signs in it. It goes through
and updates each record. It sets
WeightScaled to the variable
@Counter plus the value
of the column Weight. Then it assigned this same value back to the variable
@Counter. You can find
out a little more about this at http://www.sqlteam.com/item.asp?ItemID=765.
It's probably easier to show you an example. In our example, I'll assume there are three ads. This is what the table will look like after the update has run.
When I select my random number from 0 through 11, any value from 0 to 2 will select
value greater than 2 through 5 will choose the second row and values greater than 5 through 11 will choose the
last row. As you can see each row's Weight affects how often it is chosen.
Any time you update an Ad, this stored procedure will update every record in the table. While that might concern
you, it shouldn't be an issue. The
Ads table is relatively static. It probably doesn't get updated
on a regular basis. It is usually updated by a webmaster who is more tolerant of a slow query knowing what is
going on behind the scenes. And finally, updating records like this is just plain fast. Bulk updating records
in a single UPDATE statement is 10 to 100 times faster than writing an ASP page that updates each record one at
a time. This table is probably cached in memory anyway since it's being queried for every single web page
displayed and that will make the UPDATE statement even faster.
In Part 3, the final part, we'll look at the most important part - actually displaying the advertisement banners!