[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2451
  • Last Modified:

Output SQL Server data as Hyperlinks

Hi,

How to convert sql server data to hyperlinks. For example I have two columns ReportName and ReportURL are stored in tblReports table. I want to write a tsql/stored procedure that gives output in HTML. And when this HTML is opened in browser It should give one column ReportName containing all report names as hyperlinks..

Your help and suggestions are appreciated.
 
Thanks.
0
ezkhan
Asked:
ezkhan
2 Solutions
 
nemws1Database AdministratorCommented:
Just use the concatenation operator ("+") to generate HTML:

SELECT '<A HREF="'
    + ReportURL
    + '">'
    + ReportName
    + '</A><BR>'
    + CHAR(10)   -- newline
FROM your_table_of_links
;

Open in new window

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
My HTML is a little rusty but here is how you can loop over a select statement to build a list...

create procedure spr_returnHTML (@reportname varchar(50)=NULL, @HTMLstringout varchar(max) output)
as 
begin
declare @HTMLHeader varchar(max), @HTMLFooter varchar(max), @storeHTML varchar(max),
select @HTMLHeader='put HTML header and initial body here', 
@HTMLFooter='put HTML footer ',
@storeHTML=''

select @storeHTML=@storeHTML+ '<br><a href="'+ ReportURL +'">'+ReportName+'</a>'
from tblReports
where reportname=isnull(@reportname , report name) --> optional parameter

select @storeHTML=@HTMLHeader+@storeHTML+@HTMLFooter

return @storeHTML
end

Open in new window

Hope this helps.
0
 
BAKADYCommented:
Both methods above can work, but I recommend you to do this in your program code and not in SQL, it just increase your data volume between program and SQL server
0
 
vivekkumarSharmaCommented:
You can make HTML from SQL but this is not recommended.

unless specific requirement, don't go for this approach.
This increase network related issues.

Try to handle this at program level.
0
 
nemws1Database AdministratorCommented:
I'm not sure what kind of network you guys are running, but my code adds a total of 19 bytes per URL/name combination.  Even if we were talking about a thousand URLs, that is a very minimal increase in the data size (less than 19 kilobytes).  An at-home 1gb network connection won't even notice that increase.

Now, I could see the argument that if this is being called 1000x per second that the network might be an issue.  However, if you have that much volume going through, you need to look at caching or a similar solution rather than worry about where your HTML code is being generated.

Finally, although good suggestions, we typically try to give the asking party the answer to their question instead of just poking holes in what they've asked.  As experts, we can point out possible issues to them (as you have done), but we often aren't given the whole story.  Perhaps this user is connecting to their database with OSQL or SQLCMD from a batch script?  In that case, there is *not* any client side processing that really can be done and they need the HTML to be generated in SQL.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now