March 22, 2012 at 12:23 pm
Hi Everyone,
I am a newbie to sql and I was wondering if there is a way to have words wrap automatically in a cell in a table created by html code in a stored procedure.
Current Code:
SET @table =
'<TABLE border=1 cellpadding=5>' +
'<tr><th width=100 align=center><B>Reimbursement Reference No.</B></th><th width=100 align=center><B>Travel Date</B></th><th width=100 align=center><B>Amount Paid</B></th><th width=100 align=center><B>Reimbursement Narrative</B></th></tr>' +
CAST
The problem is that the Reimbursement Narrative field can be several lines long and it is currently cutting it off. I tried to change the width size - but that only expands it a little. Is there a way to tell that cell to wrap the sentences or expand automatically?
Thank you for any assistance!
March 22, 2012 at 12:44 pm
take a look at this example;
what you are asking is possible by getting the data using XML;
Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
name As
,
product As
,
provider As
,
data_source As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements
take a look at this longer, full featured example; i'm building a longer string for an email, and there's a REPLACE substitution in there to get every other row a differnet color for the table.
the tough part is the headers; you have to put those together manually, the way you've already started in your example.
Declare @Body varchar(max),
@PageHead varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @PageHead = '<html>'
+ '<head>'
+ '<title> Query Results </title>'
+ '<meta name="Generator" content="Mud Table and a sharp stick.">'
+ ' <style TYPE="text/css"> '
+ ' <!-- '
+ ' BODY { background-color: #FFFFFF;font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small;}'
+ ' .mytitle { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: medium; font-weight: bold; font-style: italic;}'
+ ' H3 { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: medium; font-style: italic;font-weight: bold; text-decoration: underline;}'
+ ' .mysection{ font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: large; font-weight: bold; text-decoration: underline;}'
+ ' .mylocation{ font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: bold; text-decoration: underline;}'
+ ' .jump{ font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: normal; text-decoration: none;font-style: italic;}'
+ ' ul.master { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: normal; text-decoration: none;list-style-type: square}'
+ ' ul.slave { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; font-weight: normal; text-decoration: none;list-style-type: circle}'
+ ' table {width: 80%;border-style: solid;border-width: 1px;}'
+ ' thead { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: small; font-weight: bold; background-color: #CCCCCC; vertical-align: top;border-style: solid;border-width: 1px;}'
+ ' th { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; vertical-align: top;border-style: solid;border-width: 1px; font-weight: bold;}'
+ ' td { font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; vertical-align: top;border-style: solid;border-width: 1px;}'
+ ' --></STYLE>'
+ '</head>'
+ '<body>';
Set @TableHead = '<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr bgcolor=#FFEFD8>'
+ '<th>Server Name</th>'
+ '<th>Product</th>'
+ '<th>Provider</th>'
+ '<th>Data Source</th>'
+ '<th>Is Linked?</th></tr>';
Select @Body = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
name As
,
product As
,
provider As
,
data_source As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @PageHead + @TableHead + @Body + @TableTail
-- return output
Select @Body
Lowell
March 23, 2012 at 6:34 am
Hi Lowell,
Thank you so much for the example. I like the idea of different colors for the rows. I will play with it some and see what I can do.
This site has been so helpful to me when I'm trouble-shooting.
Thanks again and have a great weekend!:-)
Martina
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply