Wrap text when column name is too long?

Question

I have this script that sends an email:

USE msdb
EXEC sp_send_dbmail
@profile_name = 'Mail list',
@recipients = 'bob.jones@company.com',
@subject = 'query results',
@body = 'Result from SELECT appended below.',
@execute_query_database = 'DB',
@query = 'exec usp_DisplayData'

The issue is that many of the columns (or their alias) are incredibly long, so the query result in the email will look something like this:

SDMI POS Sales Name SDMI POS Purchases
------------------- ------------------
1              Joe  3

Without html, is there a way that that I can wrap the text in the columns so it will look something like this in the email?

SDMI  POS Name SDMI POS
Sales          Purchases
--------- ---- ---------
1         Joe  3

usp_DisplayData is a simple query, looks something like this:

Create procedure usp_usp_DisplayData
as
select 
cast(sales as varchar(16))  as 'SDMI POS Sales',
Name,
cast(purch as varchar(16))  as 'SDMI POS Purchases',
from Sales

Why *Without html*? This is something the presentation tool should manage... Even if you'd go into dynamically created SQL, where you might include line-breaks into a column's alias, this would not display as expected within a pure text system. — Shnugo, Dec 13 '16 at 20:36
There are tools to create `ASCII` tables (only working with fixed width fonts like `courier new`) — Shnugo, Dec 13 '16 at 20:36
Just to mention it: [This answer](http://stackoverflow.com/a/39487565/5089204) provides a fully generic approach to create `html` tables from any given `SELECT` with full support of `CSS`... — Shnugo, Dec 13 '16 at 21:15
I mean, you could theoretically just manually add the line breaks in your procedure if you needed. e.g. `cast(sales as varchar(16)) as 'sdmi pos` then on a new line `sales'` — ZLK, Dec 13 '16 at 21:22
@Shnugo, I've seen similar answers. The issue is, how do I use the function `dbo.CreateHTMLTable` with a stored procedure instead of a `SELECT` query? — fdkgfosfskjdlsjdlkfsf, Dec 14 '16 at 14:57
@ZLK No, plain text will always return to the very first character of the line. `ASCII` tables need **a lot of querky calculations!!!** — Shnugo, Dec 14 '16 at 15:50
I ended up using HTML, but I'll select your answer over the others since you seem to know your stuff and you're an upbeat kinda guy. Thanks! — fdkgfosfskjdlsjdlkfsf, Feb 16 '17 at 19:18

score 0 · Accepted Answer · edited May 23 '17 at 11:53

You will have no luck, if you try to insert line-breaks into your column aliases. Plain text will always return to the first character after a line-break. This will not be aware of columns...

If possible (depending on the target system of your eMail), you should use HTML to format a table.

In your last comment you ask about the function of the linked answer.

dbo.CreateHTMLTable() takes one parameter of type XML as input (some more if you take the version with class names) and returns one single XML (which is XHTML actually).

The trick is, that the following returns one single, scalar XML

SELECT
(
   SELECT * FROM SomeWhere FOR XML RAW,TYPE
) AS TheXmlResult;

The inner SELECT returns as XML, this is wrapped in paranthesis, thus it will be a single column in the outer select. (For details, e.g. about ELEMENTS XSINIL, read the background of the linked answer).

The call is easy (be aware of the doubled paranthesis):

SELECT dbo.CreateHTMLTable((***your select here*** FOR XML RAW,ELEMENTS XSINIL))

That's it... The return value is the full xhtml-table.

sp_send_dbmail has got a parameter to specify the format as html. I think this is

@body_format = 'HTML'

Btw: Wrong usage of a Stored Procedure

Your stored procedure is the wrong tool for this (hammering a nail with a beer bottle works, but...)

Create procedure usp_usp_DisplayData
as
select 
[...]

SPs are created to do something. Your SP does nothing else then reading. This is much better packed into a VIEW or, if there are parameters, into an inline table valued function...

You can put this SELECT directly into the statement calling the function above

DECLARE @body XML=
(
SELECT dbo.CreateHTMLTable
 (
  (
  select cast(sales as varchar(16)) as 'SDMI POS Sales'
        ,Name
        ,cast(purch as varchar(16))  as 'SDMI POS Purchases'
  from Sales 
  FOR XML RAW,ELEMENTS XSINIL
  )
 )
);

The problem is that the SP is long and rather complicated. I posted this SP as an example because I didn't know the select statement would be relevant. — fdkgfosfskjdlsjdlkfsf, Dec 14 '16 at 15:59
Do you know the structure of the returned table? If so, you might declare or create a temp table with the right structure and use `INSERT INTO TheTempTable EXEC MySP` to get your resultset into a table. This table you can use with a simple `SELECT * FROM TheTempTable`. If the result set is more complex, you might add `INTO SomeNewTableName` to the SP's final `SELECT`. Then call it and find the result in a newly create table with the name given. Use this to create a structure script... — Shnugo, Dec 14 '16 at 16:04

Wrap text when column name is too long?

1 Answers1

Btw: Wrong usage of a Stored Procedure