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
Solved

Output SQL Server data as Hyperlinks

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 54
Is this spec enough for a developer or is it just blabble ? 1 36
SQL USE DATABASE VARIABLE 5 27
Are triggers slow? 7 10
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

840 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