Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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). 
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 = '&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
     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.

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

Documenting Stored Procedures

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

ARTICLE

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...

FORUM

Documention

Instance and DB documentation

FORUM

Web Sevice Documentation

Documenting the underlying sql view

FORUM

Error in Printing the document using SQL CLR Stored Procedure

Error in Printing the document using SQL CLR Stored Procedure

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones