Return HTML From T-SQL Using CASE and LEN

by Duane 27. January 2009 02:05

I recently had to build a ASP.NET - VB.NET encyclopedia application on deities where there was some articles that had one or two images that where always in the same location in the layout and some articles that had none.  In this case I decided to return a Dataset.  If I used a DataReader instead I could loop through the result set and use VB to format my HTML depending on whether images existed or not.  In the case of a Dataset however I would have to loop through the DataGrid server control.  Not pretty at all unless you want to impress someone with how many lines of code you write.

I decided that the best (easiest) way to do it was within the SQL using the LEN and CASE functions.  What happens is if there is no image an empty char is returned otherwise <img src="yourimage.gif" alt="title's image" /> is returned.

SELECT Title,
       CASE LEN(Thumb)
       WHEN '0' THEN ''
       ELSE ('<img class="imageright" src="' + Thumb + '" alt="' + Title + '''s image">')
       END AS Thumb
FROM   P_Article

The CSS class:

  img.imageright { float: right; margin-left: 10px; margin-bottom: 10px; border:0px}

Currently rated 5.0 by 2 people

  • Currently 5/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags:

Comments

Add comment


(Will show your Gravatar icon)  

  Country flag

biuquote
  • Comment
  • Preview
Loading