Jobbing!

I want to retrieve all those records from a column (that stores date
values) where in the date is 1 + today's date. For e.g. today is
20/09/2005. I want all those records where the date is 21/09/2005.
I want to send a reminder mail to all those records retrieved that they
have to make the payment latest by tomorrow which is the due date.
That's the reason why I am fetching all those records where the date is
1 + today's date. I am implementing this by creating a job & scheduling
it to run everyday at one particular time. This is the code:
---
DECLARE
@.getduedate varchar(20),
@.msg varchar(3000),
@.email varchar(100),
@.person varchar(50)
SET @.getduedate=(SELECT DDate FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1)))
IF (@.getduedate<>"")
BEGIN
SET @.email=(SELECT EMail FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1))
SET @.person=(SELECT Person FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1))
SET @.msg='To ' + @.person + ','
SET @.msg=@.msg + 'Your payment is due for tomorrow.'
EXEC master.dbo.xp_sendmail
@.recipients=@.email,
@.subject='Payment Due Date Reminder!',
@.message=@.msg
END
---
But the above generates the "Subquery returned more than 1 value" error
when more than 1 record matches the criteria. How do I resolve this?
Thanks,
Arpan
You have to do that in a loop if it contains more than one rows
(untested)
DECLARE
@.getduedate varchar(20),
@.msg varchar(3000),
@.email varchar(100),
@.person varchar(50),
@.RowCount int,
@.I INT
SET @.I = 0
CREATE TABLE #Mails
(
Counter INT identity(1,1),
DDate varchar(200),
EMail varchar(200),
Person varchar(200)
)
INSERT INTO #Mails(DDate,EMail,Person)
SELECT DDate,EMail,Person FROM MyTable WHERE
DDate=CONVERT(char(20),GETDATE()+1,1)
SET @.Rowcount = @.@.Rowcount
WHILE @.I < @.RowCount
BEGIN
Select @.getduedate= DDate,
@.email = EMail,
@.person = Person,
@.msg = 'To ' + @.person + ',' + 'Your payment is
due for tomorrow.'
FROM #Mails
Where Counter = @.I
EXEC master.dbo.xp_sendmail
@.recipients=@.email,
@.subject='Payment Due Date Reminder!',
@.message=@.msg
END
HTH, jens Suessmeyer.|||Sorry should be :
WHILE @.I <= @.RowCount|||Thanks, Jens, for your help although a couple of minor issues gave me a
big headache :-)
There was no code to increment the variable @.l at the end of the WHILE
loop which was why it created an infinite loop!
Secondly the temp table #Mails is being created with an Identity column
initialized to 1 & incrementing by 1 for subsequent records but you
have initialized @.l to 0 which is why the job wasn't succeeding since
xp_sendmail wasn't getting any value for the mandatory @.email parameter
when @.l=0!
Anyways, thanks a lot once again for your help. I really appreciate the
efforts & time you have put in to help me out.
BTW, isn't there any other approach other than what you have shown (no
cursors......please)?
Regards,
Arpan