Re: Is there a cleaner way to do this?
From: MGFoster (me_at_privacy.com)
Date: 12/17/04
- Next message: Alejandro Mesa: "Re: concatenate function"
- Previous message: David C: "Not allow range within range"
- In reply to: Jeff Metcalf: "Is there a cleaner way to do this?"
- Next in thread: Jeff Metcalf: "Re: Is there a cleaner way to do this?"
- Reply: Jeff Metcalf: "Re: Is there a cleaner way to do this?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 17 Dec 2004 20:14:13 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Why do you have a 2nd SELECT statement after the declaration of @email,
@subject & @message? The cursor declaration uses the same SELECT
statement and the first FETCH will overwrite the variables set in that
2nd SELECT statement.
You have a couple BEGINs...ENDs to many. If you just want to prettify
the code:
DECLARE curr_notify CURSOR FOR
SELECT RTRIM(serviceemail)+';'+RTRIM(otheremail) AS Email,
'Call number'+' '+RTrim(callnbr) as subject,
'Call number'+' '+RTrim(callnbr)+' '+'for'+' '+
RTRIM(custnmbr)+' at '+RTRIM(adrscode)+', '+RTRIM(svcdescr)+',
'+' has had the status changed to '+RTRIM(stsdescr)+'.'+
' For more details, go to http://www.computer-plus.com to ' +
' view your service calls.' AS Message
FROM sendcall70C
WHERE sentmail IS NULL
DECLARE @email VARCHAR(100),
@subject VARCHAR(50),
@message VARCHAR(2000)
OPEN curr_notify
FETCH NEXT FROM curr_notify INTO @email, @subject, @message
WHILE @@fetch_status = 0
BEGIN
EXEC master..xp_sendmail
@recipients = @email, subject=@subject, @message=@message
FETCH NEXT FROM curr_notify INTO @email, @subject, @message
END
CLOSE curr_notify
DEALLOCATE curr_notify
UPDATE SENDCALL70C
SET sentmail = null
--set this to '1' when done testing
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQcM9wIechKqOuFEgEQJAJQCfdSuKYETsIXZjCaAfORhifMAXivYAoOPF
2eBr6wMjBCDY/E4tykXAWQgA
=lyFv
-----END PGP SIGNATURE-----
Jeff Metcalf wrote:
> Declare curr_notify cursor for
> select RTRIM(SERVICEEMAIL)+';'+RTRIM(OTHEREMAIL) AS Email,
> 'Call number'+' '+RTrim(callnbr) as subject,
> 'Call number'+' '+RTrim(callnbr)+' '+'for'+' '+
> RTRIM(custnmbr)+' at '+RTRIM(adrscode)+', '+RTRIM(svcdescr)+', '+' has had
> the status changed to '+RTRIM(STSDESCR)+'.'+
> ' For more details, go to http://www.computer-plus.com to view your
> service calls.'as Message
> from sendcall70C WHERE SENTMAIL IS NULL
>
> DECLARE @email varchar(100), @subject varchar(50),@message varchar(2000)
> SELECT @email = RTRIM(SERVICEEMAIL)+';'+RTRIM(OTHEREMAIL),
> @subject='Call Number'+' '+RTrim(callnbr),
> @message='Call number'+' '+RTrim(callnbr)+' '+'for'+' '+
> RTRIM(custnmbr)+' at '+RTRIM(adrscode)+', '+RTRIM(svcdescr)+', '+' has had
> the status changed to '+RTRIM(STSDESCR)+'.'+
> ' For more details, go to http://www.computer-plus.com to view your
> service calls.'
> from SENDCALL70C WHERE SENTMAIL IS NULL
>
> open curr_notify
>
> fetch next from curr_notify into @email, @subject, @message
> while (@@fetch_status =0)
> Begin
> begin
>
> EXEC master..xp_sendmail @recipients = @email, @subject=@subject,
> @message=@message
>
> end
> fetch next from curr_notify into @email, @subject, @message
> End
> CLOSE curr_notify
> DEALLOCATE curr_notify
>
> update SENDCALL70C
> set SENTMAIL=null
> --set this to '1' when done testing
>
> It works...just seems like kind of a mess.
- Next message: Alejandro Mesa: "Re: concatenate function"
- Previous message: David C: "Not allow range within range"
- In reply to: Jeff Metcalf: "Is there a cleaner way to do this?"
- Next in thread: Jeff Metcalf: "Re: Is there a cleaner way to do this?"
- Reply: Jeff Metcalf: "Re: Is there a cleaner way to do this?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
