Convert to For/Next

Question

Convert to For/Next

Arthur Lorenzini

SSC-Addicted

Points: 447
More actions
January 29, 2007 at 2:31 pm

#116072

Any ideas on how to convert this function from cursor base to a for/next loop?

CREATE function GetRespondentFinal(
@RespondentID int,
@SurveyInstanceID int)
returns int
as
begin
declare @xfinal int;
set @xfinal=null;
declare abc cursor for
select max(mfinal) from (
select MAX(sie.Final) mfinal
from EventLog el, surveyInstanceEvents sie
where el.respondentid = @respondentid
   and el.eventID = sie.eventID
   and sie.SurveyInstanceID = @SurveyInstanceID
union all
   select MAX(e.FinalCode)
    from EventLog el, events e
   where el.respondentid = @respondentid
   and el.eventID = e.eventID
          and not exists (select 'x'
             from surveyinstanceevents sie
             where sie.eventID = el.eventID
               and sie.surveyinstanceID = @surveyinstanceID
             )
&nbsp f;
set @xfinal=null;
open abc;
fetch abc into @xfinal;
close abc;
return ISNULL(@xfinal,0)
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dclark SSC Eights! Points: 843 More actions · Answer 1

CREATE function GetRespondentFinal(
  @RespondentID  int,
  @SurveyInstanceID int)
returns int
as
begin
declare @xfinal int;
set @xfinal=null;
select  @xfinal=max(mfinal) from (
 select  MAX(sie.Final) mfinal
 from EventLog el, surveyInstanceEvents sie
 where el.respondentid = @respondentid
   and el.eventID = sie.eventID
   and sie.SurveyInstanceID = @SurveyInstanceID
 union all
   select  MAX(e.FinalCode)
    from EventLog el, events e
   where el.respondentid = @respondentid
   and el.eventID = e.eventID
          and not exists (select 'x'
             from surveyinstanceevents sie
             where sie.eventID = el.eventID
               and sie.surveyinstanceID = @surveyinstanceID
             ) f

 
return ISNULL(@xfinal,0)
end

Arthur Lorenzini SSC-Addicted Points: 447 More actions · Answer 2

Thank you for your response. i tried doing this way but it dies on the first row. I need the ability to loop through a bunch of rows without using a cursor.

dclark SSC Eights! Points: 843 More actions · Answer 3

The function you posted is a scalar function, which means it can only return a single value.

If you want it to return more than one value, it will have to be converted to a table valued function.

You didn't really expain what you are trying to do, the tables you are using, or what the data looks like, so I can't give you any more help than that.

Arthur Lorenzini SSC-Addicted Points: 447 More actions · Answer 4

This function is called from within a update trigger. For instance let say I have 1000 respondents that insert into the respondent table. This update trigger which runs the function is run each time the update runs.

What is happening, is the whole process is timing out after say 500 rows are inserted. I have got the issue tracked down to this particular function because for whatever reason it throws a table lock which causes a block on the calling stored procedure.

schleep SSChampion Points: 12585 More actions · Answer 5

You say this is called from an update trigger, and then give an example involving one 1000 row insert (or 1000 single row inserts). Either way, unless the trigger is insert/update, "that ain't it".

Your conclusion re: locking is suspect, as triggers run under the same process as the calling sproc.

Going back to your original post, yes you can probably convert a cursor into a for/next, but why bother? You'd be substituting one form of row-by-row processing for another.

So, can you explain what it is you're trying to accomplish?

Thinking about it a little more, it occurs to me that you might be in an endless loop.

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

Arthur,

Take a look here:

open abc;

fetch abc into @xfinal;

close abc;

Why are you using a cursor to fetch one row from a select query?