Most large, database driven, websites have some mechanism
for dynamically creating hyperlinks within their pages. The importance of dynamically creating hyperlinks is obvious - its very difficult to maintain
hard-coded hyperlinks in hundreds or thousands of documents. The need for dynamic hyperlinks arises when you want certain
words to be displayed as hyperlinks, for all documents on your site. For example, you want every instance of the word "Microsoft" on your site to be displayed as a link that goes to a Microsoft website. But more
often, you want users to be able to redirect to another document within your site, for every instance of a particular word. The below stored procedure (spAddLinks) inserts hyperlinks at the time a document is called, by searching through a keyword table
The strategy of this stored procedure (spAddLinks) is to:
- Loop through a list of keywords
- Scan the document for each keyword
- When an instance of a keyword is found, replace it with a hyperlink
- Return the modified document with the added hyperlinks
The first step is to create the tables to be used by the stored procedure.
The stored procedure assumes the below tables are created in your database (tblTags & tblDocuments).
CREATE TABLE [dbo].[tblTags]( [TagKey] [int] IDENTITY(1,1) NOT NULL, [Tag] [varchar](255) NOT NULL, [TagLink] [varchar](1000) NOT NULL)
The "Tag" field is the keyword that will be searched for within the document. When
the "Tag" is found within the document, it is replaced with a hyperlink that references
the TagLink field. The replaced
text will look something like,
<a href="TagLink Field">"Tag Field"</a>
The table would look something like,
Populating, the table "tblTags" is another project by itself. A logical process
would be to have content editors enter keywords into this table after authoring
a document (create some application interface for that). They would enter keywords
that are relevant to the document (in the Tag field), and a relative path to the
document for those keywords (in the TagLink field). Additionally, some mechanism
should be put in place to avoid duplicate Tag fields, either by placing a unique
index on the Tag field, or checking for duplicates through application validation.
CREATE TABLE [dbo].[tblDocuments]( [DocumentKey] [int] IDENTITY(1,1) NOT NULL, [DocumentHTML] [text] NOT NULL)
The table "tblDocuments" is a fictional table that is assumed
to contain all your sites articles, documents, etc. The "DocumentHTML" field would contain the raw or
HTML for your documents. The below stored procedure assumes that documents
are being stored as encoded HTML. Encoded HTML, encodes dangerous characters that
could potentially fire a malicious script. If you store documents without encoding, insert actual
text for the characters "<", ">" ,""" at Line 32 below,
and remove < , >
, and "
Also, references to this table will have to be replaced with the
table(s) that your site's documents use (see Line 13 in stored procedure).
CREATE PROCEDURE spAddLinks @DocumentKey int as SET NOCOUNT ON --place document into temporary table for manipulation CREATE TABLE #Document ( DocumentKey int, DocumentHTML text) --Line 13: you will need to replace tblDocuments with your own databases document table and related fields insert into #Document (DocumentKey, DocumentHTML) select DocumentKey, DocumentHTML from tblDocuments where DocumentKey = @DocumentKey --loop through tags and look for a match in the document declare @Tag varchar (255), @TagLink varchar (1000) DECLARE Tag_Cursor CURSOR FOR SELECT tblTags.Tag, tblTags.TagLink FROM tblTags Order By tblTags.Tag OPEN Tag_Cursor FETCH NEXT FROM Tag_Cursor into @Tag, @TagLink WHILE @@FETCH_STATUS = 0 BEGIN declare @ReplaceString varchar(2000) --Line 32 set @ReplaceString = '<A href="' + @TagLink + '">' + @Tag + '</A>' declare @TextPointer varbinary(16) declare @DeleteLength int declare @OffSet int SELECT @TextPointer = textptr(DocumentHTML) FROM #Document where DocumentKey = @DocumentKey set @DeleteLength = len(@Tag) set @OffSet = 0 WHILE (select count(*) FROM #Document where DocumentKey = @DocumentKey and (patindex( '% ' + @Tag + ' %', DocumentHTML) <> 0) ) > 0 BEGIN SELECT @OffSet = patindex( '% ' + @Tag + ' %', DocumentHTML) FROM #Document WHERE DocumentKey = @DocumentKey and (patindex( '% ' + @Tag + ' %', DocumentHTML) <> 0) UPDATETEXT #Document.DocumentHTML @TextPointer @OffSet @DeleteLength @ReplaceString END FETCH NEXT FROM Tag_Cursor into @Tag, @TagLink END CLOSE Tag_Cursor DEALLOCATE Tag_Cursor select DocumentKey, DocumentHTML from #Document where DocumentKey = @DocumentKey
The above procedure provides a basic template for dynamically inserting hyperlinks
via stored procedure. In using the above strategy on my own websites, it has performed
very well. And if you're like me, you like to move as much coding to stored procedures
as I can.