Solved

Output SQL Server data as Hyperlinks

Posted on 2013-06-12
5
1,341 Views
Last Modified: 2013-06-20
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
Comment
Question by:ezkhan
5 Comments
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 250 total points
ID: 39242892
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
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 250 total points
ID: 39242916
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
 
LVL 4

Expert Comment

by:BAKADY
ID: 39243599
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
 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39243770
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
 
LVL 23

Expert Comment

by:nemws1
ID: 39244817
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now