SQLTeam.com | Weblogs | Forums

Removing multiple cursors into set based


#1

I was changing the sp that use cursors into set based process.The cursors was using lot of business logics. I was able to convert almost everything but now I am stuck to the point where it is using another cursor inside that cursor.Do anyone know or is there any article regarding how to change that inside cursors into set logics.

Thanks a lot guys.


#2

Is is possible to show the code?


#3

DECLARE @id varchar(50),
@name varchar(50),
@address varchar(50),
@Template varchar(100)

declare Cursornumber1 for
select id,name,address,template FROM tablename

OPEN Cursornumber1

FETCH NEXT FROM Cursornumber1 INTO @id,

@name,
@address,
@Template

while (@@fetch_status = 0) begin
----all the business logics

Create table #Information
(InNumber varchar(50),
EmailContact varchar(50),
EmailAddress varchar(50))

INSERT INTO #Information(InNumber,EmailContact,EmailAddress)
SELECT InNumber,EmailContact,EmailAddress FROM tabletwo WHERE InNumber=@id

DECLARE CursorNumber2 for
SELECT InNumber,EmailContact,EmailAddress FROM #Information
open CursorNumber2
fetch next from CursorNumber2 INTO
@InNumber,
@EmailContact,
@EmailAddress,

while (@@fetch_status = 0) begin

set @template=@name+@EmailContact

exec usp_addtemplate
end

close CursorNumber2
deallocate CursorNumber2
drop TABLE #Information

exec usp_SendInfo

fetch NEXT FROM Cursornumber1
INTO  @id,

@name,
@address,
@Template

close CursorNumber1
deallocate CursorNumber1

so in this logic --set @template=@name+@EmailContact--now how do i join between the first and second cursors,,as the column template is using the logic from both the cursors--also this is not the real code,,just the scenario I am facing while converting cursors into set based.