Move through rows in table

KevCB226 · #1 (**permalink**) 01-06-09, 09:48

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

blindman · #2 (**permalink**) 01-06-09, 10:30

Are you sending off one e-mail per action, or one e-mail per user?

KevCB226 · #3 (**permalink**) 01-06-09, 10:32

It's one e-mail per action.

Just realised I had put table instead of query, so I've corrected the post

tomh53 · #4 (**permalink**) 01-06-09, 10:37

What is the value of @@ROWCOUNT after the IF (SELECT ... )?

I suspect it is 1, so you would only get 1 row before your @k is > @rows thereby ending your loop.

Try:

Code:

 
select @rows = count(*) 
from dbo.tblActions   
WHERE dbo.tblActions.acti_complete = 0 
   AND COALESCE(dbo.tblActions.acti_due_datetime,getdate()) < GETDATE()

blindman · #5 (**permalink**) 01-06-09, 10:43

Set up a cursor that selects the primary key of all the actions that need to be e-mailed. Then loop through the cursor, storing the primary key value in a variable and using it to filter and identify the rest of the information that needs to be included in the e-mail.

KevCB226 · #6 (**permalink**) 01-06-09, 11:26

I tried adding a cursor to the query before, but it told me I couldn't do this due to the local variables.

This is what I did:

Code:

DECLARE overdue_cursor CURSOR
FOR SELECT @email = dbo.tblUsers.user_email, @notes = dbo.tblActions.acti_notes, @subject = dbo.tblActions.acti_subjectFROM 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
FETCH NEXT FROM overdue_cursor

tomh53 · #7 (**permalink**) 01-06-09, 11:43

That is not a valid cursor. The cursor declaration select the columns. The fetch assigns them to the variables.

Code:

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
FETCH NEXT FROM overdue_cursor into @email, @notes, @subject
WHILE @@FETCH_STATUS = 0
BEGIN
   <processing steps>
   FETCH NEXT FROM overdue_cursor into @email, @notes, @subject
END
 
CLOSE CURSOR overdue_cursor
 
DEALLOCATE overdue_cursor

KevCB226 · #8 (**permalink**) 01-06-09, 12:00

Thanks Tom, I'll give it a go tomorrow

KevCB226 · #9 (**permalink**) 01-07-09, 05:35

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

pootle flump · #10 (**permalink**) 01-07-09, 05:45

Code:

   IF EXISTS (SELECT NULL FROM dbo.tblActions WHERE dbo.tblActions.acti_complete = 0 AND dbo.tblActions.acti_due_datetime < GETDATE())
    BEGIN

KevCB226 · #11 (**permalink**) 01-07-09, 07:10

It's soo close and almost there, but still not quite right.

The select you posted (poots) has stopped that error, and it now works, but the else part of the statement doesn't work.

The following errors are returned:

Quote:

Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 65
A cursor with the name 'upcoming_cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 67
A cursor with the name 'upcoming_cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 87
A cursor with the name 'upcoming_cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 89
A cursor with the name 'upcoming_cursor' does not exist.

So I then copied the IF EXISTS and swapped the date filter around, which does get rid of the errors, but then I don't get any e-mails via the sendmail for actions that will be due in the future.
I only get e-mailed about actions that are overdue.

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(555), @subject nvarchar(75), @msg nvarchar(755)

    IF EXISTS (SELECT NULL FROM dbo.tblActions WHERE dbo.tblActions.acti_complete = 0 AND dbo.tblActions.acti_due_datetime < 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
        IF EXISTS (SELECT NULL FROM dbo.tblActions WHERE dbo.tblActions.acti_complete = 0 AND dbo.tblActions.acti_due_datetime > GETDATE())
    BEGIN
        -- 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
END

pootle flump · #12 (**permalink**) 01-07-09, 07:24

Why not open one cursor and test the date?

KevCB226 · #13 (**permalink**) 01-07-09, 10:17

Wahey, it works!!.
Thanks, I should of used that structure in the first place methinks