Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Angry Unanswered: stored procedure loop ?

    I have a procedure that takes 1 parameter, phase. I need it to select info from a table where phase = @phase and emailsent <> 'Y'

    Then I need it to loop through those rows and update the emailsent flag on the current row to Y

    This is what I have, and it isn't updating the row. (I'm new to stored procedures ... can you tell ?) What am I doing wrong / missing ???

    Here's my code : (I'm forcing @Phase to 1 right now)

    CREATE PROCEDURE [dbo].[sp_HousingControl]
    AS

    declare @studentid varchar(10)
    declare @email varchar(50)
    declare @room varchar(20)
    declare @studentname varchar(65)
    declare @Phase int
    SET @Phase = 1

    DECLARE rs CURSOR FOR
    SELECT studentid,email,roomid,studentname
    FROM ho_assigned_students
    WHERE phase = @Phase and emailsent <> 'Y'

    OPEN rs
    FETCH NEXT FROM rs INTO @studentid,@email,@room,@studentname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    UPDATE ho_assigned_students
    SET emailsent = 'Y' where studentid = '@studentid'
    FETCH NEXT FROM rs INTO @studentid,@email,@room,@studentname
    END

    CLOSE rs
    DEALLOCATE rs

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: stored procedure loop ?

    Originally posted by netlurch
    :
    WHILE @@FETCH_STATUS = 0
    BEGIN
    UPDATE ho_assigned_students
    SET emailsent = 'Y' where studentid = '@studentid'
    FETCH NEXT FROM rs INTO @studentid,@email,@room,@studentname
    END
    :

    first of all, you should write
    SET emailsent = 'Y' where studentid = @studentid

    but why don't you update directly ?
    UPDATE ho_assigned_students
    SET emailsent = 'Y' WHERE phase = @Phase and emailsent <> 'Y'

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    Re: stored procedure loop ?

    Originally posted by msieben
    first of all, you should write
    SET emailsent = 'Y' where studentid = @studentid

    but why don't you update directly ?
    UPDATE ho_assigned_students
    SET emailsent = 'Y' WHERE phase = @Phase and emailsent <> 'Y'
    Because what I left out from the explaination was that I actually need to use the other fields, construct the body of an email message and call another procedure to send the email, then update the field to Y.

    Thanks for the tips!! I was dinking around with my query and got it to work. Here's what I ended up with (had to have double quotes in the select statement :

    CREATE PROCEDURE [dbo].[sp_HousingControl]
    AS

    declare @studentid varchar(10)
    declare @email varchar(50)
    declare @room varchar(20)
    declare @studentname varchar(65)
    declare @Phase int
    SET @Phase = 1

    DECLARE rs CURSOR FOR
    SELECT studentid,email,roomid,studentname from ho_assigned_students where phase = @Phase and emailsent <> "Y"
    OPEN rs
    FETCH NEXT FROM rs INTO @studentid,@email,@room,@studentname

    WHILE @@FETCH_STATUS = 0
    BEGIN

    UPDATE ho_assigned_students
    SET emailsent = 'Y' where studentid = @studentid
    FETCH NEXT FROM rs INTO @studentid,@email,@room,@studentname

    END

    CLOSE rs
    DEALLOCATE rs

  4. #4
    Join Date
    Feb 2003
    Posts
    109

    congrats

    congrats you've just taught a half-million db kids how to send spam.

    thanks.
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

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