CODEWITHSUNDEEP
sql-serverstored-procedures
m b
m b

Reputation: 3

SQL Server : nested stored procedure to update table values

I have a table structure and its data as follows.

CREATE TABLE TestTable 
(
    id INT Identity(1,1) PRIMARY KEY,
    creationTimestamp DATE,
    indexOne INT,
    indexTwo INT
);

INSERT INTO TestTable (creationTimestamp, indexOne, indexTwo) 
VALUES 
(2014-01-10, 100, 0),
(2014-01-11, 100, 0),
(2014-01-12, 100, 0),
(2014-01-13, 152, 2),
(2014-01-14, 152, 2),
(2014-01-15, 152, 2),
(2014-02-12, 152, 2),
(2014-02-13, 152, 2),
(2014-02-14, 333, 4),
(2014-02-15, 333, 4),
(2014-02-16, 333, 4),
(2014-03-10, 333, 4),
(2014-03-11, 333, 4),
(2014-03-12, 333, 4),
(2014-03-13, 333, 4),
(2014-03-14, 333, 4),
(2014-04-20, 500, 7),
(2014-04-21, 500, 7),
(2014-04-22, 500, 7),
(2014-04-23, 500, 7);

When you consider indexOne + indexTwo, there are duplicate rows. But I need them to be unique.

Therefore indexTwo must be properly indexed as follows

(2014-01-10, 100, 0),
(2014-01-11, 100, 1),
(2014-01-12, 100, 2),
(2014-01-13, 152, 0),
(2014-01-14, 152, 1),
(2014-01-15, 152, 2),
(2014-02-12, 152, 3),
(2014-02-13, 152, 4),
(2014-02-14, 333, 0),
(2014-02-15, 333, 1),
(2014-02-16, 333, 2),
(2014-03-10, 333, 3),
(2014-03-11, 333, 4),
(2014-03-12, 333, 5),
(2014-03-13, 333, 6),
(2014-03-14, 333, 7),
(2014-04-20, 500, 0),
(2014-04-21, 500, 1),
(2014-04-22, 500, 2),
(2014-04-23, 500, 3);

I have written the following stored procedure and it does not work properly

declare @indexOne int, @indexTwo int, @x int
declare c cursor for
select indexOne, indexTwo
from TestTable
group by indexOne, indexTwo 

open c
fetch next from c into @indexOne, @indexTwo
while @@FETCH_STATUS = 0
begin
    set @x = 0;             
    declare @id int

    declare c1 cursor for
    select id
    from TestTable
    where indexOne = @indexOne and indexTwo = @indexTwo
    order by creationTimestamp asc  

    open c1
    fetch next from c1 into @id

    while @@FETCH_STATUS = 0                    
    begin
        UPDATE TestTable SET indexTwo = @x WHERE id = @id   
        set @x = @x + 1 
        fetch next from c1 into @id
    end

    close c1
    deallocate c1
    fetch next from c into @indexOne, @indexTwo

end
close c
deallocate c

Help me to find why this is not working

Upvotes: 0

Views: 203

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

You don't need a cursor to do this use window function to generate the indextwo values per creationtimestamp, indexone. I hope this will do the job.

Sql Server

UPDATE A 
SET    indexTwo = b.indexTwo  
FROM   testtable a 
       JOIN (SELECT creationTimestamp, indexOne, 
                    Row_number()OVER(partition BY indexone 
                        ORDER BY creationtimestamp)-1 indexTwo  
             FROM   testtable) B 
         ON a.creationtimestamp = b.creationtimestamp 
            AND a.indexone = b.indexone

SQLFIDDLE DEMO

Upvotes: 1

Related Questions