Replace Cursors using temp tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Replace Cursors using temp tables

Author

Topic

EASYSQL
Starting Member

10 Posts

Posted - 2006-03-30 : 09:22:45

Can i have a sample code where a cursor is being replaced by Temp table.
I have the following scenario.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create PROCEDURE DBO.P_INSP_RELEASE_PROPERTY
(
@PROCESS_ID INT,
@MSG_TXT VARCHAR(1000) OUTPUT
)
AS
SET NOCOUNT ON

DECLARE @MRTG_LOAN_NUMBER VARCHAR(30),
@BANK_ID SMALLINT,
@BANK_CLIENT VARCHAR(30),
@LOAN_TYPE VARCHAR(30),
@BANK_FOR VARCHAR(30),

DECLARE INSP_PROPERTY CURSOR FOR
SELECT
DILOAN AS MRTG_LOAN_NUMBER,
BID AS BANK_ID,
DIDEPT AS BANK_CLIENT,
DILTYPE AS LOAN_TYPE,
DIBNKNAM AS BANK_FOR,

FROM
INSP_HDR_INTFC (nolock)
WHERE
PROCESS_ID = @PROCESS_ID

OPEN INSP_PROPERTY

FETCH NEXT FROM INSP_PROPERTY INTO
@MRTG_LOAN_NUMBER, @BANK_ID, @BANK_CLIENT, @LOAN_TYPE, @BANK_FOR
IF @@FETCH_STATUS <> 0
BEGIN
SELECT @MSG_TXT = 'PROPERTY cursor not executed; continuing'
END

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE DBO.PROPERTY SET
BANK_ID = @BANK_ID,
BANK_DEPT = @BANK_DEPT,
BANK_CLIENT = @BANK_CLIENT,
BANK_LOAN_TYPE = @LOAN_TYPE,
BANK_FOR = @BANK_FOR,
WHERE
MRTG_LOAN_NUMBER = @MRTG_LOAN_NUMBER

END

CLOSE INSP_PROPERTY
DEALLOCATE INSP_PROPERTY

SELECT @MSG_TXT = 'Property stored procedure executed successfully'

RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-03-30 : 09:40:24

You don't need a cursor, or even a temp table, to accomplish this:

CREATE PROCEDURE DBO.P_INSP_RELEASE_PROPERTY ( @PROCESS_ID INT, @MSG_TXT VARCHAR(1000) OUTPUT ) AS
SET NOCOUNT ON

UPDATE A SET BANK_ID = B.BANK_ID, BANK_DEPT = B.BANK_DEPT, BANK_CLIENT = B.BANK_CLIENT, BANK_LOAN_TYPE = B.LOAN_TYPE
FROM DBO.PROPERTY A INNER JOIN NSP_HDR_INTFC ON A.MRTG_LOAN_NUMBER = B.MRTG_LOAN_NUMBER
WHERE B.PROCESS_ID = @PROCESS_ID

SELECT @MSG_TXT = CASE @@ROWCOUNT
WHEN 0 THEN 'PROPERTY cursor not executed; continuing'
ELSE 'Property stored procedure executed successfully' END

RETURN 0

I left the original output messages intact, but the first one doesn't really make sense anymore.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources