Afternoon peeps,
I need some help on a stored procedure I'm creating.
Here are the facts:
At work we have a web based CRM and amongst various details stored in the database (SQL Server 2000), it also stores actions created by users.
What I'm trying to do is setup a stored procedure that checks these actions.
If they are not completed and overdue or not completed and are near the due date, I want to send an e-mail to the user to remind them or bring to their attention that it is overdue.
So I set about setting up SQL Mail and started creating the stored procedure.
Here is my problem:
So far I've got my stored procedure working, but it only gets one row of data from the query.
I fire off an e-mail with the details of the users action, but it only returns the details in the last row.
How do I select the details from each row in the query, rather than just one row?
Here is my procedure so far:
Code:
ALTER PROCEDURE [dbo].[sp_CRMActions]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- SET Variables
DECLARE @email nvarchar(50), @notes nvarchar(255), @subject nvarchar(75), @msg nvarchar(255), @rows int, @k int
IF (SELECT dbo.tblActions.acti_due_datetime FROM dbo.tblActions WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL)) < GETDATE()
BEGIN
SET @rows = @@ROWCOUNT
SET @k = 1
WHILE @k <= @rows
BEGIN
SELECT @email = dbo.tblUsers.user_email
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime < GETDATE())
SELECT @notes = dbo.tblActions.acti_notes
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime < GETDATE())
SELECT @subject = dbo.tblActions.acti_subject
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime < GETDATE())
-- Insert statements for procedure here
SELECT dbo.tblActions.acti_user_id, dbo.tblActions.acti_subject, dbo.tblActions.acti_priority, dbo.tblActions.acti_notes, dbo.tblUsers.user_email
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime < GETDATE())
SET @msg = 'An action you created in the CRM is overdue.' + CHAR(13) + CHAR(10) +
'Please go back and sheck the status of your action. Here is a description of the action:' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@subject + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@notes + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
'Regards,' + CHAR(13) + CHAR(10) +
'The MIS Team'
-- Send the e-mail
EXEC master.dbo.xp_sendmail
@recipients = @email,
@subject = 'You have an overdue action in the CRM which needs attention!',
@message = @msg;
SET @k = @k + 1
END
END
ELSE
SELECT dbo.tblActions.acti_due_datetime
FROM dbo.tblActions
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime > GETDATE())
SET @rows = @@rOWCOUNT
SET @k = 1
WHILE @k <= @rows
BEGIN
SELECT @email = dbo.tblUsers.user_email
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime > GETDATE())
SELECT @notes = dbo.tblActions.acti_notes
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime > GETDATE())
SELECT @subject = dbo.tblActions.acti_subject
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime > GETDATE())
-- Insert statements for procedure here
SELECT dbo.tblActions.acti_user_id, dbo.tblActions.acti_subject, dbo.tblActions.acti_priority, dbo.tblActions.acti_notes, dbo.tblUsers.user_email
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime > GETDATE())
SET @msg = 'An action you created in the CRM is almost due.' + CHAR(13) + CHAR(10) +
'Please go back and sheck the status of your action. Here is a description of the action:' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@subject + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@notes + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
'Regards,' + CHAR(13) + CHAR(10) +
'The MIS Team'
-- Send the e-mail
EXEC master.dbo.xp_sendmail
@recipients = @email,
@subject = 'You have an upcoming action in the CRM which needs attention!',
@message = @msg;
SET @k = @k + 1
END
END