We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Cursor replacement

jorbroni
jorbroni asked
on
Medium Priority
959 Views
Last Modified: 2012-05-06

I have a stored procedure that I am trying to optimize. There is a cursor being use in the stored procedure that I am trying to figure out a better alternative.

Is there is a better way of processing these records without the use of this cursor. I'm concern that as we process more and more records going forward, I don't the stored procedure processing records one at a time.
DECLARE cur CURSOR READ_ONLY
	FOR SELECT * FROM #TempTriggerCandidate
	
	OPEN cur
	
	FETCH NEXT FROM cur INTO
		@Location_Id,
		@HouseHoldId,
		@CustomerId,
		@TriggerId2,
		@TriggerStatusId2,
		@Vin,
		@DealerDataId,
		@StartDate,
		@EndDate,
		@Merge_Birthdate
	
	WHILE @@FETCH_STATUS =0
	BEGIN
		
		--insert into TriggerCandidate
		INSERT INTO TriggerCandidate (Location_Id, HouseHoldId, CustomerId, TriggerId, TriggerStatusId, Vin, DealerDataId, BroadcastModeId, StartDate, EndDate, CreateDateTime)
							VALUES (@Location_Id, @HouseHoldId, @CustomerId, @TriggerId, @TriggerStatusId, @Vin, @DealerDataId, @BroadcastModeId, @StartDate, @EndDate, @IncludeDateTime)
		
		--get new TriggerCandidateId
		SELECT @NewTriggerCandidateId = SCOPE_IDENTITY()
		
		--insert into TriggerCandidateMergeField
		INSERT INTO dbo.TriggerCandidateMergeField (TriggerCandidateId,	[Key], Value) VALUES (@NewTriggerCandidateId, 'Birthdate', @Merge_Birthdate)
 
		--fetch next
		FETCH NEXT FROM cur INTO
			@Location_Id,
			@HouseHoldId,
			@CustomerId,
			@TriggerId2,
			@TriggerStatusId2,
			@Vin,
			@DealerDataId,
			@StartDate,
			@EndDate,
			@Merge_Birthdate
			
	END	
	
	CLOSE cur
	DEALLOCATE cur

Open in new window

Comment
Watch Question

Commented:
The trigger can be replaced if you follow the following pseudo-code template -

1) Create an identity column in #TempTriggerCandidate
2) Start an explicit transaction [BEGIN TRAN]
3) Reset the Identity seed value in #TempTriggerCandidate to match TriggerCandidate.TriggerCandidateId using DBCC CHECKIDENT(#TempTriggerCandidate, RESEED, <max value of TriggerCandidate.TriggerCandidateId>)

4) Populate the table #TempTriggerCandidate
5) SET IDENTITY_INSERT TriggerCandidate ON
6) Populate the table TriggerCandidate with explicity values for TriggerCandidateID  from #TempTriggerCandidate
7) Populate the table TriggerCandidateMergeField directly from #TempTriggerCandidate
8) SET IDENTITY_INSERT TriggerCandidate OFF
9) COMMIT TRAN


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
IF TriggerCandidate has a natural key, then you can do it without a cursor.
--insert into TriggerCandidate
  INSERT INTO TriggerCandidate (Location_Id, HouseHoldId, CustomerId, TriggerId, TriggerStatusId, Vin, DealerDataId, BroadcastModeId, StartDate, EndDate, CreateDateTime)
  SELECT @Location_Id, @HouseHoldId, @CustomerId, @TriggerId, @TriggerStatusId, @Vin, @DealerDataId, @BroadcastModeId, @StartDate, @EndDate, @IncludeDateTime
  FROM #TempTriggerCandidate
		
 		
--insert into TriggerCandidateMergeField
INSERT INTO dbo.TriggerCandidateMergeField (TriggerCandidateId,	[Key], Value) 
  SELECT CandidateId, 'Birthdate', Merge_Birthdate
  FROM #TempTriggerCandidate TTC inner join TriggerCandidate TC 
    ON TTC.naturalkey = TC.naturalkey    --replace with join on actual key
 
		

Open in new window

Author

Commented:

Sorry, I'm not familiar with natural keys. Are they like composite keys?

Commented:
CandidateID (I'm guessing at the actual column name) is a surrogate key because it is generated by the Identity propery and has no meaning to your business.  The natural key is one or more columns besides that one that uniquely identify the row.  

To do this without a cursor, you need a natural key to join TriggerCandidate to the temp table in order to extract the surrogate key.  

Insert into TriggerCandicate
Select Location_Id, HouseHoldId, CustomerId, TriggerId, TriggerStatusId, Vin, DealerDataId, BroadcastModeId, StartDate, EndDate, IncludeDateTime 
From #TempTriggerCandidate
 
Insert into TriggerCandidateMergeField
Select (Select IsNull(Max(TriggerCandidateMergeField),0) + 1) as Id 
From TriggerCandidateMergeField),'Birthdate', @Merge_Birthdate 
From  #TempTriggerCandidate

Open in new window

Author

Commented:
Thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.