LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Cursors Replacement

Cursors Replacement

Options

Previous Topic · Next Topic

jch09

Posted: Friday, January 16, 2009 3:11:52 AM

Rank: Newbie

Joined: 1/16/2009
Posts: 7
Points: -226

Hello again,

I need to improve the performance of the following trigger:

CREATE trigger [ATTENDANCE_INSUPDDEL] on [dbo].[ATTENDANCE]
for update,insert,delete
as
declare @r int
declare cur cursor for select distinct registration from inserted union select distinct registration from deleted
open cur
fetch next from cur into @r
while @@fetch_status=0
begin
exec sync_attendance @r
fetch next from cur into @r
end
close cur
deallocate cur

Below is the code of sync_attendance:

CREATE procedure [dbo].[sync_attendance] @registration int
as
declare @student int
declare @semester int
set @student=(select distinct student from registration where code=@registration)
set @semester=(select distinct semester from registration where code=@registration)
delete from _totalgrade where student=@student and semester=@semester
insert into _totalgrade select distinct * from fn_totalgrade(@student, @semester)
delete from _stud_progress where student=@student and psem>=@semester
insert into _stud_progress select distinct * from stud_progress where psem>=@semester and student=@student

thanks in advance!

Scott Whigham

Posted: Thursday, January 22, 2009 8:44:58 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

I don't think this is a horrible technique. There are other techniques but I can't say that they are better without knowing more info. Some people say, "All cursors are evil." This is potentially one of those areas where a cursor is not so evil.

Now, if you absolutely don't want to do a cursor here, you are still going to have to have some way of iterating through your set:

Code:

DECLARE @MyHoldingTable TABLE (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Registration VARCHAR(255)
INSERT @MyHoldingTable
SELECT DISTINCT Registration FROM inserted
UNION
SELECT DISTINCT Registration FROM deleted

DECLARE @Key INT. @r INT

SELECT @Key = MIN(ID) FROM @MyHoldingTable

WHILE @Key IS NOT NULL
  BEGIN
      SELECT @r = Registration FROM @MyHoldingTable WHERE Id=@Key
      EXEC sync_attendance @r

      DELETE @MyHoldingTable WHERE Id=@Key
      SET @Key = NULL
      SELECT @Key = MIN(ID) FROM @MyHoldingTable
  END

jch09

Posted: Monday, February 02, 2009 4:08:34 AM

Rank: Newbie

Joined: 1/16/2009
Posts: 7
Points: -226

thanks for your reply I will try it,,,

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.