Thanks guys,
I've managed to get one of the the cursors working.
The second cursor works great, but the first cursor doesn't get run at all.
I think it's the IF...ELSE statement causing the problem, as when I run the procedure I get this error:
Quote:
Msg 512, Level 16, State 1, Procedure sp_CRMActions2, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
|
The IF...ELSE statement was put in to determine whether the action is overdue or not.
Code:
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)
IF (SELECT dbo.tblActions.acti_due_datetime FROM dbo.tblActions WHERE EXISTS
(SELECT dbo.tblActions.acti_id FROM dbo.tblActions) AND (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL)) < GETDATE()
BEGIN
-- Set cursor to run through rows in query
DECLARE overdue_cursor CURSOR FOR
SELECT dbo.tblUsers.user_email, dbo.tblActions.acti_notes, 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())
OPEN overdue_cursor
-- Get the values from the query and store them in the variables
FETCH NEXT FROM overdue_cursor INTO @email, @notes, @subject
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set the e-mail message
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;
-- Get values from next row
FETCH NEXT FROM overdue_cursor INTO @email, @notes, @subject
END
CLOSE overdue_cursor
DEALLOCATE overdue_cursor
END
ELSE
-- Set cursor to run through rows in query
DECLARE upcoming_cursor CURSOR FOR
SELECT dbo.tblUsers.user_email, dbo.tblActions.acti_notes, 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())
OPEN upcoming_cursor
-- Get the values from the query and store them in the variables
FETCH NEXT FROM upcoming_cursor INTO @email, @notes, @subject
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set the e-mail message
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;
-- Get values from next row
FETCH NEXT FROM upcoming_cursor INTO @email, @notes, @subject
END
CLOSE upcoming_cursor
DEALLOCATE upcoming_cursor
END