Can Cursors be replaced in this Stored proc

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)

Can Cursors be replaced in this Stored proc

Author

Topic

EASYSQL
Starting Member

10 Posts

Posted - 2006-04-05 : 10:54:44

Can i replace the cursors in the following sp.
Please help
thanks

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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

DECLARE @ERR INT,
@DECMTTXT_APPEND VARCHAR(6000),
@DIGRPSEQ BIGINT,
@DEWOID DECIMAL(11,0),
@DECOID DECIMAL(11,0),
@DECMTSEQ VARCHAR(10),
@DECMTTXT VARCHAR(6000),
@INSP_ORDER_ID BIGINT

--select top 10 * from INSP_HDR_INTFC
DECLARE INSP_CMNT_INTFC CURSOR FOR
SELECT DISTINCT
A.DIWOID,
A.DICOID,
A.DIGRPSEQ
FROM
INSP_CMNT_INTFC A (nolock)
JOIN INSP_HDR_INTFC B (nolock)
ON A.DIWOID = B.DIWOID
AND A.DICOID = B.DICOID
AND B.PROCESS_ID = @PROCESS_ID

ORDER BY
A.DIWOID,
A.DICOID,
A.DIGRPSEQ

OPEN INSP_CMNT_INTFC

FETCH NEXT FROM INSP_CMNT_INTFC INTO @DEWOID, @DECOID, @DIGRPSEQ

IF @@FETCH_STATUS <> 0
BEGIN
SELECT @MSG_TXT = 'COMMENTS cursor not executed; continuing'
GOTO ENDOFCOMMENTS
END

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE INSP_CMNT_INTFC_INNER CURSOR FOR
SELECT
B.ORDER_ID,
A.DICMTSEQ,
rtrim(A.DICMTTXT)
FROM
INSP_CMNT_INTFC A (nolock)
JOIN ORDERS B (nolock)
ON A.DIWOID = B.WORK_ORDER_ID
AND A.DICOID = B.OLPIN_COID

WHERE
A.DIWOID = @DEWOID
AND A.DICOID = @DECOID
AND A.DIGRPSEQ = @DIGRPSEQ

ORDER BY
A.DIGRPSEQ,
A.DICMTSEQ

OPEN INSP_CMNT_INTFC_INNER

FETCH NEXT FROM INSP_CMNT_INTFC_INNER INTO @INSP_ORDER_ID, @DECMTSEQ, @DECMTTXT

SELECT @DECMTTXT_APPEND = ''

IF @@FETCH_STATUS <> 0
BEGIN
SELECT @MSG_TXT = 'INNER COMMENTS cursor not executed; continuing'
GOTO ENDOFINNERCOMMENTS
END

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DECMTTXT_APPEND = @DECMTTXT_APPEND + ' ' + rtrim(@DECMTTXT)
FETCH NEXT FROM INSP_CMNT_INTFC_INNER INTO @INSP_ORDER_ID, @DECMTSEQ, @DECMTTXT
END

IF DATALENGTH(ltrim(@DECMTTXT_APPEND)) > 0
BEGIN
IF EXISTS(Select top 1 comment from INSP_RSLT_CMNT (nolock) where order_Id = @INSP_ORDER_ID and DIGRPSEQ = @DIGRPSEQ)
BEGIN UPDATE INSP_RSLT_CMNT
SET Comment = ltrim(@DECMTTXT_APPEND)
WHERE order_Id = @INSP_ORDER_ID and DIGRPSEQ = @DIGRPSEQ and ITEM_CODE='CMTTXT'

END
ELSE
INSERT INTO INSP_RSLT_CMNT
(ORDER_ID, FORM_VERSION, ITEM_CODE, COMMENT, MODIFY_DT, DIGRPSEQ)
VALUES
(@INSP_ORDER_ID, 'MAIN', 'CMTTXT', ltrim(@DECMTTXT_APPEND), GETDATE(), @DIGRPSEQ)

SELECT @ERR = @@ERROR
IF @ERR <> 0
BEGIN
SELECT @MSG_TXT = 'Error encountered during INSP_RSLT_CMNT insert for ORDER_ID ' + convert(varchar(30),@INSP_ORDER_ID)
CLOSE INSP_CMNT_INTFC_INNER
DEALLOCATE INSP_CMNT_INTFC_INNER
CLOSE INSP_CMNT_INTFC
DEALLOCATE INSP_CMNT_INTFC
RETURN -1
END
END

ENDOFINNERCOMMENTS:

CLOSE INSP_CMNT_INTFC_INNER
DEALLOCATE INSP_CMNT_INTFC_INNER

FETCH NEXT FROM INSP_CMNT_INTFC INTO @DEWOID, @DECOID, @DIGRPSEQ
END

ENDOFCOMMENTS:

CLOSE INSP_CMNT_INTFC
DEALLOCATE INSP_CMNT_INTFC

SELECT @MSG_TXT = 'Comments 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-04-05 : 15:05:38

Since you already have an example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63977

How about taking a stab at it yourself and posting your progress? I'd prefer to feel like I'm helping, not doing your job for you.

EASYSQL
Starting Member

10 Posts

Posted - 2006-04-05 : 16:55:21

when youre a beginner and dont know much abt T-SQL programming you definetly need some help or a suggestion.That was rude.
thanks anyway

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-04-05 : 19:40:36

Well if that was rude, consider you're posting code (again) asking for a rewrite (again) without acknowledging previous help or having made some effort of your own. Regardless of beginner status, it sounds just like every other I-want-someone-to-do-everything request we get on SQL Team. Sorry if I jumped to conclusions there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources