Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Move through rows in query

    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
    Last edited by KevCB226; 01-06-09 at 11:29. Reason: Was table, now query
    <- Hides behind a rock.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you sending off one e-mail per action, or one e-mail per user?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    It's one e-mail per action.

    Just realised I had put table instead of query, so I've corrected the post
    <- Hides behind a rock.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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()

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    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
    <- Hides behind a rock.

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Sep 2005
    Posts
    240
    Thanks Tom, I'll give it a go tomorrow
    <- Hides behind a rock.

  9. #9
    Join Date
    Sep 2005
    Posts
    240
    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:

    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
    Last edited by KevCB226; 01-07-09 at 07:53.
    <- Hides behind a rock.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
       IF EXISTS (SELECT NULL FROM dbo.tblActions WHERE dbo.tblActions.acti_complete = 0 AND dbo.tblActions.acti_due_datetime < GETDATE())
        BEGIN
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2005
    Posts
    240
    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:

    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
    <- Hides behind a rock.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why not open one cursor and test the date?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2005
    Posts
    240
    Wahey, it works!!.
    Thanks, I should of used that structure in the first place methinks
    <- Hides behind a rock.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •