Need to return query results to a variable or string to use with sp_send_dbmail

  • I need to use sp_send_dbmail to send an email message.

    The way I have it setup is a sproc runs every X minutes, looks for a certain type of problem that it finds via a SELECT statement.

    If that problem is encountered it then sends an email message and needs to include the contents of that select statement.

    The initial select statement dumps its results into a @TableVariable.

    Is there a way I can take the results and pass those results to sp_send_dbmail's body text?

    I don't want to tell sp_send_dbmail to execute a NEW query and send those results, I want to use the results I already stored in my table variable.

    Thanks!

  • The documentation on sp_send_dbmail includes rules on attaching query results. If that won't do for what you need, what I often do is turn the query results into an HTML string.

    Looks like this:

    create table #T (

    ID int identity primary key,

    Col1 varchar(100),

    Col2 varchar(100));

    insert into #T (Col1, Col2)

    select 'Grid 1:1', 'Grid 1:2' union all

    select 'Grid 2:1', 'Grid 2:2';

    declare @BodyGrid varchar(max);

    select @BodyGrid = coalesce(

    @BodyGrid

    + '<tr><td>' + cast(ID as varchar(100))

    + '</td><td>' + Col1

    + '</td><td>' + Col2

    + '<tr>',

    '<tr><td>' + cast(ID as varchar(100))

    + '</td><td>' + Col1

    + '</td><td>' + Col2

    + '<tr>')

    from #T;

    select @BodyGrid = '<table border="1">' + @BodyGrid + '</table>';

    select @BodyGrid;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Makes sense, thanks!

    The issue with using the query attachment option is that if I do that it executes the query in a new session so I wouldn't have access to any of the @tableVariables I was using so I'd have to rerun all my "do I need to actually send this email" queries all over again which would seem to be wasteful.

    I was using that SELECT COALESCE trick to build a comma separated string and just send that, but the HTML is a far superior presentation format.

    Thanks!

  • You're welcome.

    You can get pretty fancy with it pretty easily. It's easy to add column headers (just add them as the top row of the grid when you add the table definition). You can use case statements to add font changes. And so on.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 4 posts - 1 through 3 (of 3 total)

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