As you learned in an earlier tip, ASP is slow when it comes to formatting text. In addition, it has few functions for manipulating dates. For this reason, I decided to let SQL Server handle some of my date work in a page I built listing books I’ve written. Here’s the page in question:
<a href="http://www.northcomp.com/res_bookstore.asp">http://www.northcomp.com/res_bookstore.asp</a>
And here’s the stored procedure I used:
CREATE procedure sp_RetrieveBookList
As
SELECT B.pkBookID, B.Name, B.Description, B.ISBN,
'Publication Date: ' + DateName(month, B.PubDate) + ' ' + Cast(Year(B.PubDate) As varchar(4)) As DatePublished,
'/pics/book_' + B.ISBN + '.gif' As BookGraphic,
'<a href="' + RTrim(S.URL) + '">' + RTrim(S.Name) + '</a>' As WebSite
FROM tblBooks B, tblSites S
WHERE b.fkSiteID = S.pkSiteID
Order By B.PubDate Desc
We use SQL Server’s built in functions for extracting the month name and year of the publication date. Instead of returning those values, we put them together with the text we need and return the whole thing as a single field. We also stitch the ISBN number with a directory and a file extension to create the local URL to point to the book cover graphic. Finally, we return the support web site name and URL together as a single field.
Note that we used a Cast function around the result of the Year function. This is because SQL Server assumes that since you are adding a string (the month name and space) to a number, you must want to convert the whole thing to a number. This causes an error, but using the Cast function eliminates the problem.
If you’re working with an affiliate program, such as one run by online bookstores, you could easily create multiple URLs using the book’s ISBN number and the affiliate URL provided by your bookstore. Again, leave the string manipulation on the server, where it will run faster.