Looping through Cursor and executing Stored Procedure. Stored procedure runs only once for one record

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  January 16, 2015 at 6:02 am

  #300407

  The stored procedure only runs once. if I replace the running of stored procedure with select command it displays all the records.

  /******************* CURRENT CODE **************************************/

  DECLARE @ITEM ItemType, @Oper_Num OperNumType, @Infobar InfobarType

  DECLARE MynewCursor CURSOR FOR

  SELECT JOB.ITEM, JOBROUTE.oper_num FROM

  JOB

  JOIN JOBROUTE ON dbo.job.job = dbo.jobroute.job AND dbo.job.suffix = dbo.jobroute.suffix

  WHERE JOB.ITEM = 'shashiTest01'

  AND JOB.TYPE = 'S'

  AND JOBROUTE.WC NOT IN ('MTL', 'MATL', 'CAGE', 'INSP', 'SHIP')

  OPEN MynewCursor -- open the cursor

  FETCH NEXT FROM MynewCursor

  INTO @item, @oper_num

  --SELECT @item, @oper_num

  EXEC KI_Copy_QCItemTestSp 'MasterQualityItem', '10', @item, @oper_num, @Infobar OUTPUT

  WHILE @@FETCH_STATUS = 0

  BEGIN

  FETCH NEXT FROM MynewCursor

  INTO @item, @oper_num

  --SELECT @item, @oper_num

  EXEC KI_Copy_QCItemTestSp 'MasterQualityItem', '10', @item, @oper_num, @Infobar OUTPUT

  END

  CLOSE MynewCursor

  DEALLOCATE MynewCursor

  /******************* Good To Have **************************************/

  Alternatively if I can replace the following code and run the stored procedure inside the select command that will be great.

  Something like this

  SELECT

  JOB.ITEM,

  JOBROUTE.oper_num ,

  /*** Run Stored Procedure here - EXEC KI_Copy_QCItemTestSp 'MasterQualityItem', '10', JOB.ITEM, JOBROUTE.oper_num, @Infobar OUTPUT ****/

  FROM

  JOB

  JOIN JOBROUTE ON dbo.job.job = dbo.jobroute.job AND dbo.job.suffix = dbo.jobroute.suffix

  WHERE JOB.ITEM = 'shashiTest01'

  AND JOB.TYPE = 'S'

  AND JOBROUTE.WC NOT IN ('MTL', 'MATL', 'CAGE', 'INSP', 'SHIP')

• gregwhite83

  SSC Veteran

  Points: 243

  More actions

  January 16, 2015 at 6:21 am

  #1770732

  What about using a while loop instead?

  http://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 16, 2015 at 6:40 am

  #1770739

  your cursor is not designed correctly.

  it gets the first record, does nothing, gets the next record, processes it, and then hits the END.

  the last statement before END must be the Fetch Next

  OPEN MynewCursor -- open the cursor

  FETCH NEXT FROM MynewCursor

  INTO @item, @oper_num

  --SELECT @item, @oper_num

  EXEC KI_Copy_QCItemTestSp 'MasterQualityItem', '10', @item, @oper_num, @Infobar OUTPUT

  WHILE @@FETCH_STATUS = 0

  BEGIN

  INTO @item, @oper_num

  --SELECT @item, @oper_num

  EXEC KI_Copy_QCItemTestSp 'MasterQualityItem', '10', @item, @oper_num, @Infobar OUTPUT

  --this is the part that was messed up: must be the last statemetn before the END/ section of WHILE loop

  FETCH NEXT FROM MynewCursor

  END

  CLOSE MynewCursor

  DEALLOCATE MynewCursor

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 19, 2015 at 2:11 pm

  #1771240

  @lowell: I tend to disagree that design you posted will actually work as expected (beyond the typo FETCH NEXT FROM MynewCursor without moving INTO @item, @oper_num to the end, too):

  The first row is fetched and the processed outside the loop.

  Inside the loop this very same values are processed again.

  I'd rather use the following code:

  OPEN MynewCursor -- open the cursor

  FETCH NEXT FROM MynewCursor

  INTO @item, @oper_num

  --SELECT @item, @oper_num

  WHILE @@FETCH_STATUS = 0

  BEGIN

  --SELECT @item, @oper_num

  EXEC KI_Copy_QCItemTestSp 'MasterQualityItem', '10', @item, @oper_num, @Infobar OUTPUT

  --this is the part that was messed up: must be the last statemetn before the END/ section of WHILE loop

  -- including the variables

  FETCH NEXT FROM MynewCursor INTO @item, @oper_num

  END

  CLOSE MynewCursor

  DEALLOCATE MynewCursor

  Edit: as a side note: what's inside the the sproc KI_Copy_QCItemTestSp? Maybe the c.u.r.s.o.r. *cough* can be replaced completely...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]