Using Cursor or Nested Cursor - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Using Cursor or Nested Cursor

Author

Topic

sally123
Starting Member

1 Post

Posted - 2014-07-09 : 04:33:11

Hi All,
I've created a new table called: "Table_Excel" which has 3 columns: "ListId", "DictionaryId", "ActioDate"

the data in this table is like this:
18, 25, '02/02/2014'
18, 31, '05/06/2014'
18, 4, '03/03/2010'
732 , 25, '01/01/2011'
732 , 31, '02/02/2011'
732, 4 , '03/03/2011'
ok.
then I want to update "ActionDate" field which is in another table called: "Table2" with the values from the above table ("Table_Excel")
I wrote a cursor for this purpose:


Create PROCEDURE [dbo].[sp_Update_tblWorkflowEvent_Excel_nested]
AS

set nocount on
Declare     @ListingId int,
            @DictionaryId int,
            @ActionedDate datetime


Declare curP cursor For

  select * from [dbo].Table_Excel group by listingid,DictionaryId,   ActionedDate
   for UPDATE OF ActionedDate

OPEN curP 
Fetch Next From curP Into @ListingId, @DictionaryId,@ActionedDate

While @@Fetch_Status = 0 Begin
    print @ListingId
    print @DictionaryId
    print @ActionedDate
    
  update table2 
  set ActionedDate = @ActionedDate
  where WorkflowEventTypeId = (select WorkflowEventTypeId from [vw_WorkflowEvents_Construction] 
							   where ListingId = @ListingId and  
							   DictionaryId = @DictionaryId) 
  

Fetch Next From curP Into @ListingId, @DictionaryId,@ActionedDate

End -- End of Fetch

Close curP
Deallocate curP

set nocount off

The result of this query update the ActionDate in Table2 regardless of the ListingId, It just consider the DictionaryId condition in where clause. which is wrong. I need it to update the Table2 regarding 2 conditions (ListingId and DictionaryId) not just DictionaryId.

Do any body have any idea of what's the reason of this fault? Am i need to write a nested Cursor instead?

Since, it is an urgent case, I will appreciate all your help.
Please let me know if you need any more information.
Thank you,
regards,
Sally

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-09 : 04:43:05


DECLARE @Table_Excel Table(
	ListId INT,
	DictionaryId INT ,
	ActioDate  DATE
	)
DECLARE @Table2 Table(
	ListId INT,
	DictionaryId INT ,
	ActioDate  DATE
)

INSERT INTO @Table_Excel(ListId,DictionaryId,ActioDate)
VALUES(	18, 25, '02/02/2014'),
		(18, 31, '05/06/2014'),
		(18, 4, '03/03/2010'),
		(732 , 25, '01/01/2011'),
		(732 , 31, '02/02/2011'),
		(732, 4 , '03/03/2011')

INSERT INTO @Table2(ListId,DictionaryId,ActioDate)
VALUES(	18, 25, '02/02/2000'),
		(18, 31, '02/02/2000'),
		(18, 4, '02/02/2000'),
		(732 , 25, '02/02/2000'),
		(732 , 31, '02/02/2000'),
		(732, 4 , '02/02/2000')

select *
from 
@Table2

UPDATE T2 
SET 
	T2.ActioDate = TE.ActioDate
FROM
	@Table2 AS T2
	INNER JOIN @Table_Excel AS TE
	ON T2.DictionaryId=TE.DictionaryId
	AND T2.ListId = TE.ListId


select *
from 
@Table2

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources