SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Adding In HTML Links

By Grey Wilson,

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,
TagKey Tag TagLink
1 Microsoft http://www.msdn.com
2 Google http://www.google.com
3 Bob Jones /oursite/profiles/bobjones.html
4 About Us /oursite/company/aboutus.html

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). 
@DocumentKey int


--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) 
SELECT tblTags.Tag, tblTags.TagLink 
FROM tblTags  
Order By tblTags.Tag  
  OPEN Tag_Cursor
  into @Tag, @TagLink 

     declare @ReplaceString varchar(2000)
     --Line 32
     set @ReplaceString = '&lt;A href=&quot;' +  @TagLink + '&quot;&gt;' + @Tag + '&lt;/A&gt;'

     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
       SELECT @OffSet = patindex( '% ' + @Tag + ' %', DocumentHTML) 
       FROM #Document 
       WHERE DocumentKey = @DocumentKey and 
        (patindex( '% ' + @Tag + ' %', DocumentHTML) <> 0)   
       UPDATETEXT #Document.DocumentHTML @TextPointer @OffSet @DeleteLength @ReplaceString 
  into @Tag, @TagLink 
  CLOSE 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.

Total article views: 5562 | Views in the last 30 days: 1
Related Articles

Documenting Stored Procedures

Regular columnist Robert Marda discusses a few ideas on stored procedure documentation. How much doc...


Creating Stored Procedure Documentation with XML, SQL-DMO and XSLT

This article introduces TSQLDoc, a Windows Script Host script that extracts procedure metadata and e...



Instance and DB documentation


Error in Printing the document using SQL CLR Stored Procedure

Error in Printing the document using SQL CLR Stored Procedure


Web Sevice Documentation

Documenting the underlying sql view

stored procedures