Unable to join tables in a Cursor select | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Unable to join tables in a Cursor select

In the following code i tried to combine the cursors into 1 cursor but the results are different.The cursors are INSP_CMNT_INTFC,INSP_CMNT_INTFC_INNER
I tried left joining the orders table from the second cursor to the first cursor but the results are different.Is this possible. CREATE PROCEDURE P_COMMENTS
(
@PROCESS_IDINT,
@MSG_TXTVARCHAR(1000)OUTPUT
)
AS
SET NOCOUNT ON [email protected],
@DECMTTXT_APPENDVARCHAR(6000),
@DIGRPSEQ BIGINT,
@DEWOIDDECIMAL(11,0),
@DECOIDDECIMAL(11,0),
@DECMTSEQVARCHAR(10),
@DECMTTXTVARCHAR(6000),
@INSP_ORDER_IDBIGINT
DECLARE INSP_CMNT_INTFC CURSOR FOR
SELECTDISTINCT
A.DIWOID,
A.DICOID,
A.DIGRPSEQ
FROM
INSP_CMNT_INTFC A (nolock)
JOININSP_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’
You cannot build a query on top of a cursor. Look up "derived table" in BOL.
Easy – there is just no way for us to look at your previous post, and see what changes you’ve made. So explain what you have added/changed/removed.
Adriaan
I havent made any changes to the above but was trying the following join
to replace the above .I have tried by inserting a few rows in the tables.
THE RESULT SET SEEMS TO BE CORRECT
I am not sure though! SELECTDISTINCT
A.DIWOID,
A.DICOID,
A.DIGRPSEQ,
A.DICMTSEQ,
rtrim(A.DICMTTXT) AS DICMTTXT
FROMINSP_CMNT_INTFC A (nolock)
JOININSP_HDR_INTFC B (nolock) ON A.DIWOID = B.DIWOID
JOIN ORDERS O (nolock) ON A.DIWOID = O.WORK_ORDER_ID
AND A.DICOID = O.OLPIN_COID
AND A.DICOID = B.DICOID
AND B.PROCESS_ID = @PROCESS_ID
In the top posting, you’re declaring a cursor named INSP_CMNT_INTFC. In your latest posting, I see the same name (INSP_CMNT_INTFC) appear in a FROM clause. The only types of objects allowed in a FROM clause before SQL 2000 are: tables, views and derived tables. Starting with SQL 2000, you can also use table-returning UDFs. Starting with SQL 2005, you can also use Common Table Expressions. But never cursors. If your query is executing without a problem, then your database also contains a table or view named INSP_CMNT_INTFC. The cursor of that name is opened, but it is not being used. (If it was a UDF, then you would have to refer to it in a different manner.) The parameter names look familiar – is this related to the routine that we discussed last week?
I keep posting different scenarios by my db objects are the same.
There are no views or udf’s in the above posting .the term INSP_CMNT_INTFC, INSP_HDR_INTFC,orders
are all tables.I was trying a inner join between all the three tables because in the above sp. the table INSP_CMNT_INTFC is joined twice once with INSP_HDR_INTFC and the result is used as in the where clause when INSP_CMNT_INTFC is agained joined with the order table.
This is very confusing to me as to why the previous prgmr has joined twice and not all the three tables in multiple inner join’s
Thanks for your time
]]>