Post reply

Wrap text in a cell in a table from HTML in stored procedure

  • March 22, 2012 at 12:23 pm

    #258226

    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

    #1462962

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 23, 2012 at 6:34 am

    #1463310

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy