SQL Server Performance Forum – Threads Archive

Replace cursors Using temp tables

I know that Replacing Cursors with temp tables is effective .How can i replace the following cursor with a temp table and would it be really effective as i have read sometimes the performace is not improved significantly.
For me on this forum everyday is a learning day.Thanks to all the dedicated help from the expert advisors.
I have a cursor in one of my sp’s i have onlu copied the cursor piece.but if you want i can paste the whole sp.
DECLARE PROPERTY CURSOR FOR
SELECT ‘0000’AS PROP_ZIP4,
DILOTAS PROP_LOT,
DIBLOCKAS PROP_BLOCK,
DISECTAS PROP_SECT,
DIMADDRAS MRTG_INFO2
FROM
INSP_HDR (nolock)
WHERE
PROCESS_ID = @PROCESS_ID
OPEN INSP_PROPERTY FETCH NEXT FROM PROPERTY INTO
@PROP_ZIP4, @PROP_LOT, @PROP_BLOCK, @PROP_SECT, @MRTG_INFO2 IF @@FETCH_STATUS <> 0
BEGIN
SELECT @MSG_TXT = ‘PROPERTY cursor not executed; continuing’
END
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @RETURN_STATUS = P_PROPERTY_UPD
@PROP_ZIP4, @PROP_LOT, @PROP_BLOCK, @PROP_SECT, @MRTG_INFO2 IF @RETURN_STATUS <> 0
BEGIN
SELECT @MSG_TXT = ‘Error encountered ‘ + @MRTG_LOAN_NUMBER + ‘; aborting’
CLOSE PROPERTY
DEALLOCATE PROPERTY
RETURN -1
END FETCH NEXT FROM INSP_PROPERTY INTO
@PROP_ZIP4, @PROP_LOT, @PROP_BLOCK, @PROP_SECT, @MRTG_INFO2
END CLOSE PROPERTY
DEALLOCATE PROPERTY
SELECT @MSG_TXT = ‘Property stored procedure executed successfully’ RETURN 0

---

Post the entire stored procedure so that we can get a better idea of exactly what you are trying to accomplish

---

Here is the entire sp CREATE PROCEDURE DBO.IPROPERTY
(
@PROCESS_IDINT,
@MSG_TXTVARCHAR(1000)OUTPUT
)
AS
SET NOCOUNT ON
—From here the above code is continued.I have this sp

---

Hi ya, for anyone to have any chance of possible answering this we’d also need to see the definition for P_PROPERTY_UPD and any other dependant procs/functions that are called. >>comment removed by Twan<< Cheers
Twan

---

P_PROPERTY_UPD is another sp being called from within this sp by passing the trailing i/p params

---

Thanks for the clarification.

---

There is no way to remove this cursor without collapsing the code from within the sub-procedures into this one. If you’d like us to look at this as an option then we need to know what the sub-procedure contains…? Cheers
Twan

---

1) the proc doesn’t match the called proc’s parameter list at all…?
2) the proc doing the update will set all parameters that are not passed into NULL
3) an insert/update proc like this is usually a symptom of poor design… I’d always have seperate procs, after all the app ought to know whether the user is inserting or updating
4) removing the cursor is possible by doing an update followed by an insert … select where not exists… Cheers
Twan

---

2) the proc doing the update will set all parameters that are not passed into NULL
3) an insert/update proc like this is usually a symptom of poor design… I’d always have seperate procs, after all the app ought to know whether the user is inserting or updating
4) removing the cursor is possible by doing an update … from … followed by an insert … select … where not exists… Cheers
Twan

---

]]>