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

(tblTags).

The strategy of this stored procedure (spAddLinks) is to:

1. Loop through a list of keywords
2. Scan the document for each keyword
3. When an instance of a keyword is found, replace it with a hyperlink
4. 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

encoded

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 &lt; , &gt;

, and &quot; 

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

Conclusions

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.