Newbie Question:Looping through dataset to create email alert 2

[Jcfx]

I have a query that checks a set of data to see if I need to start charging detention on a delivery. I need to turn it into an 'email alert'. It works when the dataset returned is only one late delivery, but if there are multiple deliveries the part that builds the email fails. What would be the best way to build a loop <cursor?> for a multiple dataset? What I have so far is below. Thanks for any assistance

Declare
	@StopOrder varchar (8)
	,@Company varchar(10)
	,@SchLatest varchar (30)
	,@ArrivalDate varchar(30)
	,@DepartDate varchar(30)
	,@MinWait varchar (8)
	,@StopType varchar(5)
	,@StopEvent varchar(5)
	,@Customer varchar(10)
	,@Driver varchar(8)
	,@Tractor varchar(8)
	,@msgbody varchar (1000)
	,@sub varchar (50)


select
@StopOrder = s.ord_hdrnumber
,@Company = s.cmp_id
,@SchLatest = s.stp_schdtlatest
,@ArrivalDate =s.stp_arrivaldate
,@DepartDate = s.stp_departuredate
,@MinWait = datediff(mi,s.stp_arrivaldate,GetDate())
,@StopType = s.stp_type
,@StopEvent = s.stp_event
,@Customer = o.ord_billto
,@Driver = l.lgh_driver1
,@Tractor = l.lgh_tractor
 from 
stops s,
orderheader o,
legheader l
 where
 o.ord_status = 'STD'
    and stp_departure_status = 'OPN' --- departure is estimated 
    and stp_status = 'DNE' ---arrival date is actual
    and s.ord_hdrnumber = o.ord_hdrnumber
    and s.lgh_number = l.lgh_number
order by o.ord_billto,s.ord_hdrnumber,stp_sequence

if @MinWait >= 120

Begin

set @msgbody = @msgbody + ''
set @msgbody = @msgbody + 'Order ' + @StopOrder + ' has been at their current location for ' + char(10)
set @msgbody = @msgbody + @MinWait + ' minutes.' + char(10)
set @msgbody = @msgbody + 'Please contact '+ @Customer + char(10)
set @msgbody = @msgbody + 'Scheduled Latest ' + @SchLatest + char(10)
set @msgbody = @msgbody + 'Actual Arrival ' + @ArrivalDate + char(10)
set @msgbody = @msgbody + 'Estimated Departure ' + @DepartDate + char(10)
set @msgbody = @msgbody + 'Type ' + @StopType + char(10)
set @msgbody = @msgbody + 'Event ' + @StopEvent + char(10)
set @msgbody = @msgbody + 'Driver ' + @Driver + char(10)
set @msgbody = @msgbody + 'Tractor ' + @Tractor + char(10)

set @sub = ' '
set @sub = @sub + 'Load with Potential Detention'
set @sub = @sub + @StopOrder

exec master..xp_sendmail @recipients = 'CustomerService@ohmy.com',
		@message = @msgBody
		@subject = @sub

Julie
CRXI CE10 / RS2005 Sql DB

 

[skuhlman]

Give something like this a shot:

declare email_cursor cursor for
	select s.ord_hdrnumber,
		s.cmp_id,
		s.stp_schdtlatest,
		s.stp_arrivaldate,
		s.stp_departuredate,
		datediff(mi,s.stp_arrivaldate,GetDate()),
		s.stp_type,
		s.stp_event,
		o.ord_billto,
		l.lgh_driver1,
		l.lgh_tractor
	from stops s
		join orderheader o
			on s.ord_hdrnumber = o.ord_hdrnumber
		join legheader l
			on s.lgh_number = l.lgh_number
	where o.ord_status = 'STD'
		and stp_departure_status = 'OPN' 
		and stp_status = 'DNE' 

OPEN email_cursor

Declare
    @StopOrder varchar (8)
    ,@Company varchar(10)
    ,@SchLatest varchar (30)
    ,@ArrivalDate varchar(30)
    ,@DepartDate varchar(30)
    ,@MinWait varchar (8)
    ,@StopType varchar(5)
    ,@StopEvent varchar(5)
    ,@Customer varchar(10)
    ,@Driver varchar(8)
    ,@Tractor varchar(8)
    ,@msgbody varchar (1000)
    ,@sub varchar (50)

FETCH NEXT FROM emaiil_cursor
	INTO @StopOrder, @Company, @SchLatest, 
		@ArrivalDate, @DepartDate, @MinWait,
		@StopType, @StopEvent, @Customer,
		@Driver, @Tractor

WHILE @@FETCH_STATUS = 0
	BEGIN

	if @MinWait >= 120
		Begin
			set @msgbody = @msgbody + ''
				+ 'Order ' + @StopOrder + ' has been at their current location for ' + char(10)
				+ @MinWait + ' minutes.' + char(10)
				+ 'Please contact '+ @Customer + char(10)
				+ 'Scheduled Latest ' + @SchLatest + char(10)
				+ 'Actual Arrival ' + @ArrivalDate + char(10)
				+ 'Estimated Departure ' + @DepartDate + char(10)
				+ 'Type ' + @StopType + char(10)
				+ 'Event ' + @StopEvent + char(10)
				+ 'Driver ' + @Driver + char(10)
				+ 'Tractor ' + @Tractor + char(10)
			set @sub = ' '
				+ 'Load with Potential Detention'
				+ @StopOrder
			exec master..xp_sendmail @recipients = 'CustomerService@ohmy.com',
			        @message = @msgBody
			        @subject = @sub 
		end

		FETCH NEXT FROM emaiil_cursor
			INTO @StopOrder, @Company, @SchLatest, 
				@ArrivalDate, @DepartDate, @MinWait,
				@StopType, @StopEvent, @Customer,
				@Driver, @Tractor
	END
CLOSE email_cursor
DEALLOCATE email_cursor

 

[Jcfx]

Thanks alot, I'll give this a go.

Since I am new to this, How does it determine the fetch status? i.e. how does it know it is on the last record?

What you've given me is much cleaner code wise then other examples I've seen, just trying to understand what it is doing

Thanks again

Julie
CRXI CE10 / RS2005 Sql DB

 

[Jcfx]

Ok,I ran this a few moments ago and the select aspect locates 12 records as it should. <oh and I caught the double 'ii' in email cursor>

Of the 12 records, three met the criteria and it states in the output three records sent.

Only the subject line appears, not the body, so I am fiddling around with my original code there. And I actually received 4 emails. The very first record was sent twice.

Thanks again

Julie
CRXI CE10 / RS2005 Sql DB

 

[Jcfx]

Hello Again,

Can anyone tell me what I am missing in my code for the message body itself?

Everything is working correctly except the message body is empty. I am receiving the correct number of emails with the correct subject line, but nothing in the body of the email.

I did catch @message = @msgBody and corrected it to be @message = @msgbody

I've also used my original coding syntax for the body as well as Skulmans


It must be something really simple, since the subject line is working, but my eyes are just missing the bloody thing.


Julie
CRXI CE10 / RS2005 Sql DB

 

[gmmastros]

You are probably not initializing the @msgbody variable. You see... initially, the value is NULL. You then add strings to it, but it remains NULL.

Change:
set @msgbody = @msgbody + ''

To:

set @msgbody = ''


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jcfx]

Thank you, that was it <duh>


Julie
CRXI CE10 / RS2005 Sql DB