Replace Cursor Help

1

Hi, Gurus, I know It's not enough information I am sending out. I can't create a sample data as well.

I really appreciate If someone can guide me or show me the right path to change below CURSOR to CTE or other Option. The reason I WAS using the cursor to avoid deadlock in the table.
any advice would be great appreciated.

Thank You.

Here is my Cursor

DECLARE Order_CUSTOMER_Cursor CURSOR
FOR
	SELECT DISTINCT
		MANAGE_O_id
		,@MANAGE_O_id
        from #tempM

OPEN MANAGE_O_D_Cursor

FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id,@CUSTOMER_O_id

while @@FETCH_STATUS = 0
	BEGIN
		DECLARE @BType INT
                DECLARE @BC_Date DATETIME

IF (@C_O_id > 0 )
	BEGIN
		SELECT TOP 1
			@BType = M_Order.BType
			,@BC_Date = C_Order_D.Date
		From M_Order WITH (NOLOCK)
		    INNER JOIN M_Order_D WITH (NOLOCK) ON M_Order_D.ID = M_Order.ID
		    INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
		WHERE M_Order_D = @CUSTOMER_O_id
	
		SELECT @RcOUNT = count(*)
			From M_Order WITH (NOLOCK)
			INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
		WHERE M_Order_D = @CUSTOMER_O_id
	END
     ELSE
	BEGIN
		SELECT TOP 1
			 @BType = M_Order.BType
			,@BC_Date = C_Order_D.Date
		From M_Order WITH (NOLOCK)
			INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
		WHERE M_Order_D = @CUSTOMER_O_id

		SELECT @RcOUNT = count(*)
			From M_Order WITH (NOLOCK)
			INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
		WHERE M_Order_D = @CUSTOMER_O_id
	END

IF @RcOUNT = 0
		BEGIN
			IF ( @C_O_id > 0 )
			 BEGIN
				UPDATE Customer_D_Info
				set type = 'Active'
			END
	END
	FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id,@CUSTOMER_O_id
    END;
	CLOSE  MANAGE_O_D_Cursor
	DEALLOCATE MANAGE_O_D_Cursor
END
END


		
			

indent preformatted text by 4 spaces
2

First thing to do: get rid of NOLOCK -- it can cause you to report incorrect or deleted data or miss reporting newly inserted data. Since you are potentially updating Customer_D_Info, that is not what you want.

Second, looking at the first branch in the loop:

          SELECT TOP 1 @BType = M_Order.BType
               , @BC_Date = C_Order_D.DATE
          FROM M_Order WITH (NOLOCK)
          INNER JOIN M_Order_D WITH (NOLOCK)
               ON M_Order_D.ID = M_Order.ID
          INNER JOIN C_Order_D WITH (NOLOCK)
               ON C_Order_D.MO = M_Order_D.ID
          WHERE M_Order_D = @CUSTOMER_O_id

          SELECT @RcOUNT = count(*)
          FROM M_Order WITH (NOLOCK)
          INNER JOIN C_Order_D WITH (NOLOCK)
               ON C_Order_D.MO = M_Order_D.ID
          WHERE M_Order_D = @CUSTOMER_O_id

two questions:

  1. Why are you not including the second inner join from the query above?
  2. if you should be including the second inner join, why not just use @@ROWCOUNT instead?
3

Thank You For your reply.

The reason I can't use @@ROWCOUNT because in First Join I am linking two table (i.e customer info and customer detail table and order cancel table) In First SQL I am checking all linking should be there in 3 tables and in Second SQL Query I am checking only Customer is there and customer cancel the order if yes then how many orders. Sometimes it could a possible user don't enter the customer detail or it is kind of dummy order.

Did I answer your questions?

4

OK, I get it. Have you removed NOLOCK yet?

5

I;m trying to set up a simple test case. Are the three tables like this?

create table M_Order(Btype int , ID int , M_Order_D int)
create table M_Order_D(ID int, date datetime, M_Order_D  int)
create table C_Order_D(ID int, date datetime, MO int)
6

Yes it is.

Thank You.

7

I Guess this query will be the alternative for your cursor. Please reply if any misunderstanding in your need

UPDATE Customer_D_Info
set type = 'Active'
where not exists ( select 'x'
From M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
LEFT OUTER JOIN M_Order_D WITH (NOLOCK) ON M_Order_D.ID = M_Order.ID
WHERE M_Order_D = @CUSTOMER_O_id
and M_Order.ID = case when @C_O_id>0 then M_Order_D.ID else M_Order.ID end
)

8

@viggneshwar You shouldn't use NOLOCK.

9

Really? Your original query will not compile with those table definitions.

After adding:

create table #tempM(MANAGE_O_id int)

create table M_Order(Btype int , ID int , M_Order_D int)
create table M_Order_D(ID int, date datetime, M_Order_D  int)
create table C_Order_D(ID int, date datetime, MO int)

declare @MANAGE_O_id int, @C_O_id int, @CUSTOMER_O_id int, @rcount int

I get:

Msg 209, Level 16, State 1, Line 36
Ambiguous column name 'M_Order_D'.
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "M_Order_D.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 50
The multi-part identifier "M_Order_D.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 56
The multi-part identifier "M_Order_D.ID" could not be bound.

Can you please clean up your query to eliminate the syntax errors? Otherwise I can't be sure about any solutions I post for you