CREATE PROCEDURE [dbo].[ctg_getCategoryBreadCrumb] (
	@dept_id INT,
	@returnValue VARCHAR(1000) OUTPUT
)
AS

	DECLARE @rec_parent_id INT
	DECLARE @rec_dept_id INT
	
	DECLARE catCursor CURSOR LOCAL  FAST_FORWARD

	FOR
	SELECT parent_id, dept_id FROM cablestogo_dept
	WHERE dept_id = @dept_id

	OPEN catCursor 
	FETCH NEXT FROM catCursor INTO @rec_parent_id, @rec_dept_id
	CLOSE catCursor
	DEALLOCATE catCursor	

	IF(@rec_parent_id = 0)
		set @returnValue =  (SELECT dept_name FROM cablestogo_dept WHERE dept_id = @dept_id)
	ELSE
	BEGIN
		EXEC ctg_getCategoryBreadCrumb @rec_parent_id, @returnValue OUTPUT
		IF(@returnValue <> '')
			set @returnValue = @returnValue + '  >  ' + (SELECT dept_name FROM cablestogo_dept where dept_id = @dept_id)
		ELSE
			set @returnValue = (SELECT dept_name FROM cablestogo_dept where dept_id = @dept_id)
	END

	RETURN
GO

CREATE PROCEDURE [dbo].[ctg_UpdateFroogleFeedBreadCrumb] 
AS
	DECLARE @curr_sku INT
	DECLARE @curr_dept_id INT
	DECLARE @return_value VARCHAR(1000)
	DECLARE @i INT
	
	SET @i = 0
	DECLARE ffCursor CURSOR LOCAL  FAST_FORWARD

	FOR
	SELECT TOP 100 ff.sku, cdp.dept_id FROM froogleFeed1 ff
	INNER JOIN cablestogo_dept_prod cdp ON
	ff.sku = cdp.sku
	INNER JOIN cablestogo_dept cd ON
	cdp.dept_id = cd.dept_id
	WHERE cd.redirect IS NULL
	ORDER BY ff.sku ASC

	OPEN ffCursor 
	FETCH NEXT FROM ffCursor INTO @curr_sku, @curr_dept_id
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC ctg_getCategoryBreadCrumb @curr_dept_id, @return_value OUTPUT
		PRINT @return_value		
		FETCH NEXT FROM ffCursor INTO @curr_sku, @curr_dept_id
		SET @return_value = ''
	END
	
	Print @i
	CLOSE ffCursor
	DEALLOCATE ffCursor
GO

quote:

---

Originally posted by AndrewMurphy

1. move away from cursors....(they are really bad news
2. can you post sample input data & matching expected results....read the FAQ section to see howe to post useful information so that we can help you.

3. your problem is not the number of records you are processing...it's the number of time 1 particular record is causing the SP to call itself.

recursive SP's are not a good idea....there usually should be a better way to achieve the same result without recursion. there are some alternatives if you are on SQL2005 or SQL 2008.

---