How do I store a result set in a variable

Question

How do I store a result set in a variable

kstjacques

Ten Centuries

Points: 1349
More actions
August 20, 2009 at 9:18 am

#209795

OK, I want to use database mail to send out the result set of a query. How the heck do I create a variable that contains the entire result set?
I know the following does not work, but it will give you the general idea of what I would like to do:
DECLARE @body2 VARCHAR(1000)
@body2= SELECT * FROM myTable
WHERE myValue >= 3
AND myTime >= DATEADD(minute, -1, GetDate())
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'myMail@mycompany.com',
@body= exec @body2,
@subject = 'Test Email Subject',
@profile_name = 'DBMailProfile'
Thank you for any help you can offer!
-Kimberly

Viewing 7 posts - 1 through 6 (of 6 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

kstjacques (8/20/2009)
DECLARE @body2 VARCHAR(1000)
@body2= SELECT * FROM myTable
WHERE myValue >= 3
AND myTime >= DATEADD(minute, -1, GetDate())

May be there are more elegant ways of doing it, what i can think of is something like this...

SELECT @body1=col1+' ' +col2 + ' ' + col3 ...... FROM myTable

WHERE myValue >= 3

AND myTime >= DATEADD(minute, -1, GetDate())

Here @body1 will contain all column values seperated by spaces. I've assumed the select statement is returning only one row.

For multiple rows, i can think of a cursor where in you can add the resultset to another variable in a loop.

For each row in the recordset

@body2=@body2+@body1 + chr(13)

Pradeep Singh

GSquared SSC Guru Points: 260824 More actions · Answer 2

If you set the e-mail type as HTML, you can do something like this:

declare @Body varchar(max);

select @Body = '

';

select @Body = coalesce(@Body + '

',

'

', @Body)

from dbo.MyTable;

select @Body = @Body + '

My Column Header 1	My Column Header 2
' + MyCol1 + '	' + MyCol2 + '
' + MyCol1 + '	' + MyCol2 + '

';

Then just use that variable in the sp_send_dbmail parameter.

I've done that kind of thing many times, and it can produce a very nice looking e-mail. You might want to fiddle with the HTML a bit, to make the columns and cells look right, and you might need to cast your columns to something that'll concat with the text.

- 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

kstjacques Ten Centuries Points: 1349 More actions · Answer 3

Thank you, PS! You definitely got me moving in the right direction. I am now successfully sending results to my inbox. I have more challenges ahead of me. One is the formatting, which I will now start looking at the next responders feedback for HTML mail.

The other challenge I face is math with Nulls. when I create the body of the message, I do the following:

SET @body2= @body2 + ' ' + @col1 + ' ' + @col2 + ' ' + @col3 + ' ' + @col4 + ' ' + @col5 + ' ' + @col6 + ' ' + @col7 + ' ' + @col8 + ' ' + @col9 + char(10)

If any column returned contains a NULL, @body2 becomes empty. I'm not sure how to handle that just yet. As of right now I removed any columns in the query that contain a NULL (just for testing).

Thanks again for your help. It's much appreciated!

Kimberly

GSquared SSC Guru Points: 260824 More actions · Answer 4

Take a look at IsNull in Books Online (the help file for SQL).

If you put IsNull(MyColumn, ''), it'll put an empty space where the column would be if it's null.

- 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

kstjacques Ten Centuries Points: 1349 More actions · Answer 5

Awesome GSquared, that worked!

I had also tried 'SET CONCAT_NULL_YIELDS_NULL OFF' and that worked as well. I wasn't sure if this would have adverse effects so I'm going with your suggested IsNull approach 🙂

Kimberly

GSquared SSC Guru Points: 260824 More actions · Answer 6

The concat null thing is supposed to go away in a future version of SQL. IsNull (or Coalesce) is definitely better.

- 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