Alternatives to cursor? 1

[tsave22]

I have to run stored procedure based on the query - I've done it using cursor, but as I can see from the forum postings - cursor is bad.
Can you help me on how I can reach the same result without using cursor?

create proc stp_completAllAttendee

@classID int
as
Declare firstQuery Cursor for
SELECT loginName
FROM tblClassAttendee
where classID = @classID
for Read Only;

Declare @analystLogin char(10);
Open firstQuery;

Fetch Next from firstQuery Into @analystLogin;

WHILE (@@fetch_status <> -1)

BEGIN
EXEC stp_AttendeeComplete @classID, @analystLogin;
Fetch Next from firstQuery Into @analystLogin;
END;

close firstQuery;

deAllocate firstQuery;


GO

 

[SQLSister]

In this case what you need to do is stop using the sp that you are calling. Rewrite wahtever it does to handle data sets instead of one input at a time. Reuse of code is bad when it makes things more inefficient.

As to how to do that, well, I'd need to see what the sp actually does first. POst it and we can try to help you.

Questions about posting. See faq183-874

 

[tsave22]

I have to update status of all students registered for class based on classID and names in this class.

here is stored procedure:

create proc stp_AttendeeComplete
(
@classID int,
@loginName nvarchar(20)
)
as
update tblClassAttendee
set attendeeStatus = '6'
where
classID = @classID and loginName = @loginName


GO

 

[SQLSister]

Corect me if i'm misinterpreting, but you want otupdate the attendee status for evey login that has that classid?

Try something like this:

create proc stp_completAllAttendee

@classID int
as

update tblClassAttendee 
    set attendeeStatus = '6'   
from tblClassAttendee 
where classID = @classID

GO

Questions about posting. See faq183-874

 

[tsave22]

Yes, you are correct, thanks!!!
But I have some more work with this - I need to update another table (tblStudentData) data based on the classID, loginName and class date from tblAttendee. How I can update multiple records? I can not use where clause in this case ( at least I don't know how) because I have several different loginNames.

 

[tsave22]

here us actual stored procedure that I described:

CREATE proc stp_AnalystClassComplete

(@analystLogin nvarchar(10),
@skillID int,
@trainingScheduled nvarchar(10),
@trainingCompleted nvarchar(10))

as
declare @temp varchar(20)
declare @assessmentYear varchar(10)


set @temp = year(@trainingCompleted)
set @assessmentYear = year(@trainingCompleted)
if (month(@trainingCompleted) > '3' )
begin
set @temp = year(@trainingCompleted) + 1
end
print @temp

if (month(@trainingCompleted) < '4' )
begin
set @assessmentYear = year(@trainingCompleted) - 1
end
set @temp = substring( @temp,3,2)
set @temp = 'FY' + @temp
print @temp


update tblAnalystData
set trainingScheduled = @trainingScheduled,
trainingCompleted = @trainingCompleted,
trainingPlaned = @temp
where ( analystLogin = @analystLogin and
skillID = @skillID and
assessmentYear = @assessmentYear)



GO

 

[SQLSister]

An example of updating a table with values from another table

Update Expenses
Set NEWRc = Managers.RC
FROM  RCMap INNER JOIN Managers 
ON RCMap.UID = Managers.UID 
INNER JOIN Expenses ON RCMap.OldRC = Expenses.RC

Questions about posting. See faq183-874

 

[tsave22]

Thanks, that gives me another way to think. I appreciate your help.