Set Multi Row Record Set to @Variable

Question

Post reply

Set Multi Row Record Set to @Variable

AVB

SSChampion

Points: 11418
More actions
December 6, 2006 at 7:20 am

#114868

I am generating an email and I want to set the body of the email to the complete recordset of my query which will contain multiple rows. I know I can accomplish this by a cursor but is there an easier way to do this? Something to the effect of:
select @Body = + Invoice + ' ' + Contact form t_Table where active = 1

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

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

Yes, But In this case you will face a problem if the concated string become more than your variable size

select @Body = @Body + Invoice + ' ' + Contact form t_Table where active = 1

Before this it would be a best if you can set this option

CONCAT_NULL_YIELDS_NULL OFF or apply the NULL case on the indiual field you are using in the query

cheers

Steve Jones - SSC Editor SSC Guru Points: 736008 More actions · Answer 2

I think this is one of those cases you'd be better off controlling it. Do you know how big a row can be? If so, I'd calculate how many you can put in one email, or even get the LEN and use a cursor to process row by row.

If this is a high volume item, like every second, then you might want to SET ROWCOUNT instead and get a count of rows first and then process it batches with ROWCOUNT.

AVB SSChampion Points: 11418 More actions · Answer 3

Rao, Thank you for the response.

I'm not worried about reaching my variable size limit. Right now I'm only returning 4 rows with maybe 10 bytes each. However what you provided didn't exactly work.

I had to include the select statement twice like this:

set

CONCAT_NULL_YIELDS_NULL OFF

declare

@Body varchar(3000)

select @body = @body + Invoice from t_Table where active = 1

select @body

= @body + Invoice from t_Table where active = 1

print

@body

go

Only problem is it dupes one entry so the recordset returns 7 rows but my @body shows 8 values, one of which is repeated.

AVB SSChampion Points: 11418 More actions · Answer 4

Steve,

Thank you for the reply as well. The rowsize isn't going to be large at all for this paticular email nor will the complete record set. However we do send a lot of SQL driven mail notifications and processing it based on rowcount sounds like something I could utilize. I love to send the appropiate department notifications when something needs to be addressed or investigated.

Rao,

I actually adjusted the syntax to elimante the dupe.

set CONCAT_NULL_YIELDS_NULL OFF

declare

@Body varchar(3000)

select @body = @body + Invoice from t_Table where active = 1

select @body

= @body

print @body

go

ijaz SSCommitted Points: 1912 More actions · Answer 5

Please can you provide me the sample data u r trying, I don't think that there should be a problem you are mentioning that it will get one row extra.

why are you using the same statement twice.

cheers

AVB SSChampion Points: 11418 More actions · Answer 6

Rao,

The original statement didn't work exactly (but it was close) and utlimately helped me find my solution. It only provided results for one row. So I added a second select statement to see if that would produce different results and it did (with a duplicate row). I've since posted the new code that works correctly without dupes. Thanks again you pointed me in the right direction!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Another thing to remember, is that declared variables are unless initialized, null.

So, it's common when doing this concat trick to do;

declare @Body varchar(3000)

SET @Body = ''

.... continue with the rest.

/Kenneth