[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Output SQL Server data as Hyperlinks

Posted on 2013-06-12
5
Medium Priority
?
2,083 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1000 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 1000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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