Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to update in a cursor

Posted on 2011-02-28
26
Medium Priority
?
462 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

963 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