Solved

how to update in a cursor

Posted on 2011-02-28
26
433 Views
Last Modified: 2012-05-11
Can some one help me where I am going wrong in the code.
I created a stored procedure in sql server 2008 to update a column value  'status' by comparing rows based on the criteria mentioned in IF statements.
Please let me know if any questions

Thanks in advance!
ALTER PROCEDURE [dbo].[sp_Test2]
as 


DECLARE @prev_member_id AS int
DECLARE @prev_alt_id AS nvarchar(20)
DECLARE @prev_ssn AS nvarchar(11)
DECLARE @prev_date_of_birth AS datetime
DECLARE @prev_first_name AS nvarchar(15)
DECLARE @prev_last_name AS nvarchar(15)
DECLARE @prev_middle_init AS nvarchar(1)
DECLARE @prev_group_id AS nvarchar(10)
DECLARE @prev_plan_id AS nvarchar(10)
DECLARE @prev_plan_eff_date AS datetime
DECLARE @prev_plan_term_date AS datetime
DECLARE @prev_status AS VARCHAR


DECLARE @pres_member_id AS int
DECLARE @pres_alt_id AS nvarchar(20)
DECLARE @pres_ssn AS nvarchar(11)
DECLARE @pres_date_of_birth AS datetime
DECLARE @pres_first_name AS nvarchar(15)
DECLARE @pres_last_name AS nvarchar(15)
DECLARE @pres_middle_init AS nvarchar(1)
DECLARE @pres_group_id AS nvarchar(10)
DECLARE @pres_plan_id AS nvarchar(10)
DECLARE @pres_plan_eff_date AS datetime
DECLARE @pres_plan_term_date AS datetime
DECLARE @pres_status AS VARCHAR

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
SELECT member_id,alt_id,ssn,date_of_birth,first_name,last_name,middle_init,group_id,plan_id,plan_eff_date,plan_term_date,status
FROM [table] ORDER BY member_id,group_id,plan_id,plan_eff_date,plan_term_date

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO 

@prev_member_id,@prev_alt_id,@prev_ssn,@prev_date_of_birth,@prev_first_name,@prev_last_name,@prev_middle_init,@prev_group_id,@prev_plan_id,@prev_plan_eff_dat

e,@prev_plan_term_date,@prev_status

WHILE @@FETCH_STATUS = 0 
BEGIN

FETCH NEXT FROM @MyCursor 
INTO 

@pres_member_id,@pres_alt_id,@pres_ssn,,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_da

te,@pres_plan_term_date,@pres_status



            If 
               (@pres_ssn = @prev_ssn) And 
               (@pres_date_of_birth = @prev_date_of_birth) And 
               (@pres_last_name = @prev_last_name) And 
               (@pres_first_name = @prev_first_name) And 
               (@pres_middle_init = @prev_middle_init) And 
               (@pres_group_id = @prev_group_id) And 
               (@pres_plan_id = @prev_plan_id) AND
               (@prev_plan_eff_date = @pres_plan_eff_date) And 
               (@prev_plan_term_date = @pres_plan_term_date) 
         
BEGIN                
                    update [table] SET @prev_status = 'Delete'
                    
 END
                 
           Else
         
            If (@prev_plan_term_date = @pres_plan_eff_date) 

BEGIN
                    
                    
                   update [Table] SET @prev_status = 'Delete',
                                    @pres_plan_eff_date = @prev_plan_eff_date 
                    
                    
End
      

FETCH NEXT FROM @MyCursor 
INTO 

@pres_member_id,@pres_alt_id,@pres_ssn,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_dat

e,@pres_plan_term_date,@pres_status


 

CLOSE @MyCursor 
DEALLOCATE @MyCursor 

GO

Open in new window

ALTER PROCEDURE [dbo].[sp_Test2]
as 


DECLARE @prev_member_id AS int
DECLARE @prev_alt_id AS nvarchar(20)
DECLARE @prev_ssn AS nvarchar(11)
DECLARE @prev_date_of_birth AS datetime
DECLARE @prev_first_name AS nvarchar(15)
DECLARE @prev_last_name AS nvarchar(15)
DECLARE @prev_middle_init AS nvarchar(1)
DECLARE @prev_group_id AS nvarchar(10)
DECLARE @prev_plan_id AS nvarchar(10)
DECLARE @prev_plan_eff_date AS datetime
DECLARE @prev_plan_term_date AS datetime
DECLARE @prev_status AS VARCHAR


DECLARE @pres_member_id AS int
DECLARE @pres_alt_id AS nvarchar(20)
DECLARE @pres_ssn AS nvarchar(11)
DECLARE @pres_date_of_birth AS datetime
DECLARE @pres_first_name AS nvarchar(15)
DECLARE @pres_last_name AS nvarchar(15)
DECLARE @pres_middle_init AS nvarchar(1)
DECLARE @pres_group_id AS nvarchar(10)
DECLARE @pres_plan_id AS nvarchar(10)
DECLARE @pres_plan_eff_date AS datetime
DECLARE @pres_plan_term_date AS datetime
DECLARE @pres_status AS VARCHAR

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
SELECT member_id,alt_id,ssn,date_of_birth,first_name,last_name,middle_init,group_id,plan_id,plan_eff_date,plan_term_date,status
FROM [table] ORDER BY member_id,group_id,plan_id,plan_eff_date,plan_term_date

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO 

@prev_member_id,@prev_alt_id,@prev_ssn,@prev_date_of_birth,@prev_first_name,@prev_last_name,@prev_middle_init,@prev_group_id,@prev_plan_id,@prev_plan_eff_dat

e,@prev_plan_term_date,@prev_status

WHILE @@FETCH_STATUS = 0 
BEGIN

FETCH NEXT FROM @MyCursor 
INTO 

@pres_member_id,@pres_alt_id,@pres_ssn,,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_da

te,@pres_plan_term_date,@pres_status



            If 
               (@pres_ssn = @prev_ssn) And 
               (@pres_date_of_birth = @prev_date_of_birth) And 
               (@pres_last_name = @prev_last_name) And 
               (@pres_first_name = @prev_first_name) And 
               (@pres_middle_init = @prev_middle_init) And 
               (@pres_group_id = @prev_group_id) And 
               (@pres_plan_id = @prev_plan_id) AND
               (@prev_plan_eff_date = @pres_plan_eff_date) And 
               (@prev_plan_term_date = @pres_plan_term_date) 
         
BEGIN                
                    update [table] SET @prev_status = 'Delete'
                    
 END
                 
           Else
         
            If (@prev_plan_term_date = @pres_plan_eff_date) 

BEGIN
                    
                    
                   update [Table] SET @prev_status = 'Delete',
                                    @pres_plan_eff_date = @prev_plan_eff_date 
                    
                    
End
      

FETCH NEXT FROM @MyCursor 
INTO 

@pres_member_id,@pres_alt_id,@pres_ssn,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_dat

e,@pres_plan_term_date,@pres_status


 

CLOSE @MyCursor 
DEALLOCATE @MyCursor 

GO

Open in new window

0
Comment
Question by:ysssv
  • 9
  • 4
  • 4
  • +3
26 Comments
 
LVL 6

Expert Comment

by:anushahanna
ID: 35003821
you are doing FETCH NEXT FROM 3 times.

actually you need it only twice.

open the cursor
fetch next
WHILE @@FETCH_STATUS = 0
BEGIN
.........(do your stuff)
fetch next
END
Close Cursor and deallocate
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 35003829
also you can remove the 'else' because your two IF are totally different conditions.
0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 35003840
u r updating variables, rather than columns, eg: @prev_status  is getting updated. please make it as the column name 'status" and try
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35004334
it is impossible to know what is going on until you tell us what the purpose of the update is ....


but if you are using sql 2008 you probably don't need a cursor. even if yoju are looking at groups of 3 consecutive rows
to do an update too...

cursor processing is very time and resource consuming in sql server and should be avoided if possible.

just update the set.




0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35005848
hi

if you  are tring to update a table the fix your code to
update table set realcolumn = value.  (row 82 second code sample)
and not  update table set @variablecolumn = value.

if your tring to update a variable then use set @var = value.
0
 

Author Comment

by:ysssv
ID: 35009332
I changed the update to status = value and still not working.
Is there any other method other than using cursor.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35009694
please explain what you think you are doing in the cursor...

why the 3 fetches
why no loop

??
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35009997
hi ysssv

did you change the UPDATE in both cases ?

line 71 line 82

can you please post your changed code.
0
 

Author Comment

by:ysssv
ID: 35010716
I am trying to merge two rows in a table with the conditions to be followed -

1) Merge rows having the same key fields(key fields are seen in IF clause),

             IF(@pres_ssn = @prev_ssn) And
               (@pres_date_of_birth = @prev_date_of_birth) And
               (@pres_last_name = @prev_last_name) And
               (@pres_first_name = @prev_first_name) And
               (@pres_middle_init = @prev_middle_init) And
               (@pres_group_id = @prev_group_id) And
               (@pres_plan_id = @prev_plan_id) AND
               (@prev_plan_eff_date = @pres_plan_eff_date) And
               (@prev_plan_term_date = @pres_plan_term_date)

Then,update 'status' column name as delete

2)           IF(@pres_ssn = @prev_ssn) And
               (@pres_date_of_birth = @prev_date_of_birth) And
               (@pres_last_name = @prev_last_name) And
               (@pres_first_name = @prev_first_name) And
               (@pres_middle_init = @prev_middle_init) And
               (@pres_group_id = @prev_group_id) And
               (@pres_plan_id = @prev_plan_id) AND
               (@prev_plan_term_date = @pres_plan_eff_date)

also update status = delete when/ where the "plan_term_date" of previous (Prev) record is the same as the plan_eff_date" of the current (pres) record
               


Here is my code:

ALTER PROCEDURE dbo.sp_Test2
as
-- previous record variable declaration

DECLARE @prev_member_id AS int
DECLARE @prev_alt_id AS nvarchar(20)
DECLARE @prev_ssn AS nvarchar(11)
DECLARE @prev_date_of_birth AS datetime
DECLARE @prev_first_name AS nvarchar(15)
DECLARE @prev_last_name AS nvarchar(15)
DECLARE @prev_middle_init AS nvarchar(1)
DECLARE @prev_group_id AS nvarchar(10)
DECLARE @prev_plan_id AS nvarchar(10)
DECLARE @prev_plan_eff_date AS datetime
DECLARE @prev_plan_term_date AS datetime
DECLARE @prev_status AS VARCHAR

--current record variable declaration

DECLARE @pres_member_id AS int
DECLARE @pres_alt_id AS nvarchar(20)
DECLARE @pres_ssn AS nvarchar(11)
DECLARE @pres_date_of_birth AS datetime
DECLARE @pres_first_name AS nvarchar(15)
DECLARE @pres_last_name AS nvarchar(15)
DECLARE @pres_middle_init AS nvarchar(1)
DECLARE @pres_group_id AS nvarchar(10)
DECLARE @pres_plan_id AS nvarchar(10)
DECLARE @pres_plan_eff_date AS datetime
DECLARE @pres_plan_term_date AS datetime
DECLARE @pres_status AS VARCHAR

--declaring a cursor and trying to store the column values into cursor

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT member_id,alt_id,ssn,date_of_birth,first_name,last_name,middle_init,group_id,plan_id,plan_eff_date,plan_term_date,status
FROM [table] ORDER BY member_id,group_id,plan_id,plan_eff_date,plan_term_date


--fetching the values into previous record variables

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO

@prev_member_id,@prev_alt_id,@prev_ssn,@prev_date_of_birth,@prev_first_name,@prev_last_name,@prev_middle_init,@prev_group_id,@prev_plan_id,@prev_plan_eff_date,@prev_plan_term_date,@prev_status

WHILE @@FETCH_STATUS = 0
BEGIN

--fetching into current record variables

FETCH NEXT FROM @MyCursor
INTO

@pres_member_id,@pres_alt_id,@pres_ssn,,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_da

te,@pres_plan_term_date,@pres_status



            If
               (@pres_ssn = @prev_ssn) And
               (@pres_date_of_birth = @prev_date_of_birth) And
               (@pres_last_name = @prev_last_name) And
               (@pres_first_name = @prev_first_name) And
               (@pres_middle_init = @prev_middle_init) And
               (@pres_group_id = @prev_group_id) And
               (@pres_plan_id = @prev_plan_id) AND
               (@prev_plan_eff_date = @pres_plan_eff_date) And
               (@prev_plan_term_date = @pres_plan_term_date)
         
BEGIN                
                    update [table] SET @prev_status = 'Delete'
                   
 END
                 
         If
               (@pres_ssn = @prev_ssn) And
               (@pres_date_of_birth = @prev_date_of_birth) And
               (@pres_last_name = @prev_last_name) And
               (@pres_first_name = @prev_first_name) And
               (@pres_middle_init = @prev_middle_init) And
               (@pres_group_id = @prev_group_id) And
               (@pres_plan_id = @prev_plan_id) AND
               (@prev_plan_term_date = @pres_plan_eff_date)

BEGIN
                   
                   
                   update [Table] SET @prev_status = 'Delete',
                                      @pres_plan_eff_date = @prev_plan_eff_date
                   
                   
End
     

FETCH NEXT FROM @MyCursor
INTO

@pres_member_id,@pres_alt_id,@pres_ssn,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_dat

e,@pres_plan_term_date,@pres_status


 

CLOSE @MyCursor
DEALLOCATE @MyCursor

GO
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35011323


1st update
update [table] SET prev_status = 'Delete'
where pk_column = @Pk_value_from_cursor
                   
2nd update

-- i guess these are the real column names if not then change them
update [Table] SET prev_status = 'Delete',  pres_plan_eff_date = @prev_plan_eff_date
where pk_coloumn = @pk_value_from_cursor
                   
0
 

Author Comment

by:ysssv
ID: 35012054
No that doesnot work either
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35015772
what is the primary key of the table?
0
 

Author Comment

by:ysssv
ID: 35016494
Member id
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35016703

ok for a person (ssn  _ and name )

you are trying to set a row status to 'delete' if its not the current one...

please explain what group_id and plan_id are....

 ---> so that we can understand/confirm that they are irrelevant to setting the eff_date to the prev row eff date?

can you provide some sample data to illustrate?
0
 

Author Comment

by:ysssv
ID: 35021498
I am sorry to confuse you guys, I will give few records as an example with coulmn names-

mid- member id
gid - group
pid - plan
ped- effective date
ptd- term date
delete1 is the result from first update statement in code
delete2 is second update statement in code

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      ---> Delete1
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    ----> Delete2
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015

here are the record details for the same person , if we carefully observe 1st row,2nd row it looks like same record coming twice and so they are duplicates.my first update statement in the code should take care of these kind of records and update the status column as Delete by comparing the first and second row values.

Now if we consider 2nd row and 3rd row values we have all column values as same but PED,PTD are different so we need to update the status column of 2nd row to delete and update the PED of 3rd row column value to PED value of 2nd row column with modified in status column

4th row record has gid,pid,ptd different so they are considered to be the same .

final results should look like this

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      ---> Delete1
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    ----> Delete2
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2000     12/31/2012  -->modified
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015


Thanks to all for your efforts
0
 

Author Comment

by:ysssv
ID: 35021524
I am sorry to confuse you guys, I will give few records as an example with coulmn names-

mid- member id
gid - group
pid - plan
ped- effective date
ptd- term date
delete1 is the result from first update statement in code
delete2 is second update statement in code

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015

here are the record details for the same person , if we carefully observe 1st row,2nd row it looks like same record coming twice and so they are duplicates.my first update statement in the code should take care of these kind of records and update the status column as Delete by comparing the first and second row values.

Now if we consider 2nd row and 3rd row values we have all column values as same but PED,PTD are different so we need to update the status column of 2nd row to delete and update the PED of 3rd row column value to PED value of 2nd row column with modified in status column

4th row record has gid,pid,ptd different so they are considered to be the same .

final results should look like this

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      ---> Delete1
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    ----> Delete2
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2000     12/31/2012  -->modified
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015


Thanks to all for your efforts
0
 

Author Comment

by:ysssv
ID: 35031286
Any comments or suggestions please.
Let me know if my example explains my requirement
0
 

Author Comment

by:ysssv
ID: 35063917
Administrator/Any experts can any one guide me on my question?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35116083
About to have a look, lemme read the thread and the example data...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35116170
OK, there seems to be a fairly fundamental problem of making sure the correct rows are being updated - is there a unique identifier anywhere ?

You will have a problem if there are only two rows that are duplicated - I take it you want to keep one of them right ?

e.g. From

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015

we want

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      ---> Delete1
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    ----> Keep
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015



Similarly if there are subsequent rows

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015

we want

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      ---> Delete1
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    ----> Delete2
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012 ---> Delete3
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2000     12/31/2012  -->modified PED
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015

Also dont need two cursors, can do it with one... and might not even need cursors :)

But can you please tell us if there is a unique key anywhere ? something that can be used to uniquely identify the rows.

0
 

Author Comment

by:ysssv
ID: 35116899
true I agree we need one unique key and in my table unique value is ID which is not mentioned here and it is an auto generated value.

coming to the example which you provided

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015

we want

mid   ssn      FN      LN   MI    MC     DOB        gid     pid     PED             PTD              Status
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000    1/1/2001      ---> Delete1
10      xyz    John   Nai    p     2      2/1/2000    101        A    1/1/2000     1/1/2001    ----> Delete2
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2000     12/31/2012 ---> modified PED
10     xyz     John   Nai    p     2      2/1/2000    101       A     1/1/2001     12/31/2012  -->keep
10    xyz      John   Nai    p     2      2/1/2000    103       B     1/1/2000      1/1/2015---->keep
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 35118717
Great that we have a unique identifier column named ID - we will need that.

Not so great is the desired results - bummer - was going to first update duplicates as 'delete' - but cannot do that - we have to first resolve those dates. A cursor might be a reasonable way, but will think about that, we should be able to use a CTE.

Anyway... have a look at (and please note the use of the #Table, so do a select of [table] into [#table] whilst testing) :

DECLARE @prev_ID as int
DECLARE @prev_member_id AS int
DECLARE @prev_alt_id AS nvarchar(20)
DECLARE @prev_ssn AS nvarchar(11)
DECLARE @prev_date_of_birth AS datetime
DECLARE @prev_first_name AS nvarchar(15)
DECLARE @prev_last_name AS nvarchar(15)
DECLARE @prev_middle_init AS nvarchar(1)
DECLARE @prev_group_id AS nvarchar(10)
DECLARE @prev_plan_id AS nvarchar(10)
DECLARE @prev_plan_eff_date AS datetime
DECLARE @prev_plan_term_date AS datetime
DECLARE @prev_status AS VARCHAR(10)

--current record variable declaration

DECLARE @pres_ID as int
DECLARE @pres_member_id AS int
DECLARE @pres_alt_id AS nvarchar(20)
DECLARE @pres_ssn AS nvarchar(11)
DECLARE @pres_date_of_birth AS datetime
DECLARE @pres_first_name AS nvarchar(15)
DECLARE @pres_last_name AS nvarchar(15)
DECLARE @pres_middle_init AS nvarchar(1)
DECLARE @pres_group_id AS nvarchar(10)
DECLARE @pres_plan_id AS nvarchar(10)
DECLARE @pres_plan_eff_date AS datetime
DECLARE @pres_plan_term_date AS datetime
DECLARE @pres_status AS VARCHAR(10)

--declaring a cursor and trying to store the column values into cursor

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
SELECT id,member_id,alt_id,ssn,date_of_birth,first_name,last_name,middle_init,group_id,plan_id,plan_eff_date,plan_term_date,status
FROM [#table] ORDER BY member_id,group_id,plan_id,plan_eff_date,plan_term_date

--fetching the values into present record variables

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @pres_id,@pres_member_id,@pres_alt_id,@pres_ssn,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_date,@pres_plan_term_date,@pres_status

WHILE @@FETCH_STATUS = 0 
BEGIN

    If (@pres_ssn = @prev_ssn) And 
       (@pres_date_of_birth = @prev_date_of_birth) And 
       (@pres_last_name = @prev_last_name) And 
       (@pres_first_name = @prev_first_name) And 
       (@pres_middle_init = @prev_middle_init) And 
       (@pres_group_id = @prev_group_id) And 
       (@pres_plan_id = @prev_plan_id) AND
       (@prev_plan_eff_date = @pres_plan_eff_date) And 
       (@prev_plan_term_date = @pres_plan_term_date) 
         
       BEGIN                
           update [#table] SET status = 'Delete' where ID = @prev_id
       END

    ELSE 

    If (@pres_ssn = @prev_ssn) And 
       (@pres_date_of_birth = @prev_date_of_birth) And 
       (@pres_last_name = @prev_last_name) And 
       (@pres_first_name = @prev_first_name) And 
       (@pres_middle_init = @prev_middle_init) And 
       (@pres_group_id = @prev_group_id) And 
       (@pres_plan_id = @prev_plan_id) AND
       (@pres_plan_term_date <> @prev_plan_term_date)
       BEGIN
           update [#table] SET status = 'Delete' where ID = @prev_id
           update [#Table] SET status = 'Modified', plan_eff_date = @prev_plan_eff_date where ID = @pres_id
           set @pres_plan_eff_date = @prev_plan_eff_date
       END
      
    SET @prev_id = @pres_id
    SET @prev_member_id = @pres_member_id
    SET @prev_ssn = @pres_ssn
    SET @prev_date_of_birth = @pres_date_of_birth
    SET @prev_last_name = @pres_last_name
    SET @prev_first_name = @pres_first_name
    SET @prev_middle_init = @pres_middle_init
    SET @prev_group_id = @pres_group_id
    SET @prev_plan_id = @pres_plan_id
    SET @prev_plan_term_date = @pres_plan_term_date
    SET @prev_plan_eff_date = @pres_plan_eff_date
                    
    FETCH NEXT FROM @MyCursor 
    INTO @pres_id,@pres_member_id,@pres_alt_id,@pres_ssn,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_date,@pres_plan_term_date,@pres_status

END
 

CLOSE @MyCursor 
DEALLOCATE @MyCursor 

--GO

-- show results
select * from #table

Open in new window


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35118863
Ooops.....

Dont run the above, use this instead  (and still thinking about better ways :) ):

--ALTER PROCEDURE dbo.sp_Test2
--as 
-- previous record variable declaration

DECLARE @prev_ID as int
DECLARE @prev_member_id AS int
DECLARE @prev_alt_id AS nvarchar(20)
DECLARE @prev_ssn AS nvarchar(11)
DECLARE @prev_date_of_birth AS datetime
DECLARE @prev_first_name AS nvarchar(15)
DECLARE @prev_last_name AS nvarchar(15)
DECLARE @prev_middle_init AS nvarchar(1)
DECLARE @prev_group_id AS nvarchar(10)
DECLARE @prev_plan_id AS nvarchar(10)
DECLARE @prev_plan_eff_date AS datetime
DECLARE @prev_plan_term_date AS datetime
DECLARE @prev_status AS VARCHAR(10)

--current record variable declaration

DECLARE @pres_ID as int
DECLARE @pres_member_id AS int
DECLARE @pres_alt_id AS nvarchar(20)
DECLARE @pres_ssn AS nvarchar(11)
DECLARE @pres_date_of_birth AS datetime
DECLARE @pres_first_name AS nvarchar(15)
DECLARE @pres_last_name AS nvarchar(15)
DECLARE @pres_middle_init AS nvarchar(1)
DECLARE @pres_group_id AS nvarchar(10)
DECLARE @pres_plan_id AS nvarchar(10)
DECLARE @pres_plan_eff_date AS datetime
DECLARE @pres_plan_term_date AS datetime
DECLARE @pres_status AS VARCHAR(10)

--declaring a cursor and trying to store the column values into cursor

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
SELECT id,member_id,alt_id,ssn,date_of_birth,first_name,last_name,middle_init,group_id,plan_id,plan_eff_date,plan_term_date,status
FROM [#table] ORDER BY member_id,group_id,plan_id,plan_eff_date,plan_term_date

--fetching the values into present record variables

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @pres_id,@pres_member_id,@pres_alt_id,@pres_ssn,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_date,@pres_plan_term_date,@pres_status

WHILE @@FETCH_STATUS = 0 
BEGIN

    If (@pres_ssn = @prev_ssn) And 
       (@pres_date_of_birth = @prev_date_of_birth) And 
       (@pres_last_name = @prev_last_name) And 
       (@pres_first_name = @prev_first_name) And 
       (@pres_middle_init = @prev_middle_init) And 
       (@pres_group_id = @prev_group_id) And 
       (@pres_plan_id = @prev_plan_id) AND
       (@prev_plan_eff_date = @pres_plan_eff_date) And 
       (@prev_plan_term_date = @pres_plan_term_date) 
         
       BEGIN                
           update [#table] SET status = 'Delete' where ID = @prev_id
       END

    ELSE 

    If (@pres_ssn = @prev_ssn) And 
       (@pres_date_of_birth = @prev_date_of_birth) And 
       (@pres_last_name = @prev_last_name) And 
       (@pres_first_name = @prev_first_name) And 
       (@pres_middle_init = @prev_middle_init) And 
       (@pres_group_id = @prev_group_id) And 
       (@pres_plan_id = @prev_plan_id) AND
       (@pres_plan_term_date <> @prev_plan_term_date)
       BEGIN
           update [#table] SET status = 'Delete' where ID = @prev_id
           update [#Table] SET status = 'Modified', plan_eff_date = @prev_plan_eff_date where ID = @pres_id
           set @pres_plan_eff_date = @prev_plan_eff_date
       END
      
    SET @prev_id = @pres_id
    SET @prev_member_id = @pres_member_id
    SET @prev_ssn = @pres_ssn
    SET @prev_date_of_birth = @pres_date_of_birth
    SET @prev_last_name = @pres_last_name
    SET @prev_first_name = @pres_first_name
    SET @prev_middle_init = @pres_middle_init
    SET @prev_group_id = @pres_group_id
    SET @prev_plan_id = @pres_plan_id
    SET @prev_plan_term_date = @pres_plan_term_date
    SET @prev_plan_eff_date = @pres_plan_eff_date
                    
    FETCH NEXT FROM @MyCursor 
    INTO @pres_id,@pres_member_id,@pres_alt_id,@pres_ssn,@pres_date_of_birth,@pres_first_name,@pres_last_name,@pres_middle_init,@pres_group_id,@pres_plan_id,@pres_plan_eff_date,@pres_plan_term_date,@pres_status

END
 

CLOSE @MyCursor 
DEALLOCATE @MyCursor 

--GO

select * from #table

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now