Solved

Output SQL Server data as Hyperlinks

Posted on 2013-06-12
5
1,421 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

810 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