rewriting this without cursors?

example: if I pass Id = 564 then I should be able to read

reg_Id and ModifiedDateTime and update based on that.

here is how my table looks like for SchoolRegistration:

reg_id         id     active        modifydatetime 
-------------------------------------------------- 
432           564       1              2008-12-14 13:15:38.750         
234           564       1              2009-12-14 14:15:50.470         
432           564       1              2007-12-14 14:19:46.703

This is a performance nightmare I know that and I need help in rewriting this below sp wihout cursor, possible?

alter procedure [dbo].[Delete_Student]
    @StudentId bigint, 
    @ModifiedById bigint,
    @ModifiedDateTime datetime 
as
begin   
    begin tran 

            update  Student
            set Active = 1, 
                ModifiedById = @ModifiedById, 
                ModifiedDateTime = getdate() 
            where StudentId = @StudentId and 
              开发者_StackOverflow社区    ModifiedDateTime = @ModifiedDateTime or @ModifiedDateTime is null         

            --Registration       
            Declare @RegStudentId bigint
            DECLARE @RegistrationId bigint
            declare @RegModifiedDateTime datetime
            DECLARE CursorReg CURSOR READ_ONLY  
            FOR
                SELECT StudentId, ModifiedDateTime, RegistrationId 
                FROM Registration where StudentId = @StudentId

            OPEN CursorReg
            FETCH NEXT FROM CursorReg
            INTO @RegStudentId,@RegModifiedDateTime,@RegistrationId

            WHILE @@FETCH_STATUS = 0
            BEGIN
                update Registration
                set Active = 0,        
                ModifiedById = @ModifiedById,
                ModifiedDateTime = getdate() 
                where RegistrationId = @RegistrationId and 
                      ModifiedDateTime = @RegModifiedDateTime or @RegModifiedDateTime is null

                FETCH NEXT FROM CursorReg
                INTO @RegStudentId,@RegModifiedDateTime,@RegistrationId
            END
            CLOSE CursorReg
            DEALLOCATE CursorReg

    if (@@rowcount < 1) begin
        rollback tran
    end
    else begin
        commit tran
    end

    return @@rowcount

end 

---

For something like the registration statement, it seems you could get by with

update Registration
   set Active = 0,
       ModifiedById = @ModifiedById,
       ModifiedDateTime = getdate()
    where StudentId = @StudentId

All that looping doesn't seem to be adding anything because there's no branching logic or anything even difficult there. I didn't put that much time into analyzing the sql, but if this doesn't work then let us know why.

A quick look at the other statements makes me think you can probably do the same for all the statements.

Note that your ModifiedDateTime is going to be different for every update, so you may want to extract that out at the beginning of the stored procedure and use the same DateTime for every update.