CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user979331
user979331

Reputation: 11911

SQL trying to update record with unique value from declared table

I am having a major problem, basically I have two tables, TableA and TableB

TableA I will be inserting into then updating

TableB is a table with dateTokens, timeTokens, tokenOrder and taken

I have declared a table called @taken which copies TableB and puts its values in it.

What I am trying to do is the following:

  1. Declare @taken Table

  2. Insert a record into TableA

  3. Get the ID from the record that was just inserted into TableA via SCOPE_IDENTITY()

  4. Insert the data from TableB into @taken where taken from TableB is 0 (some records in TableB taken will be marked as 1, so I only want the records that are not taken) Also in this step, I redo the tokenOrder via Row_Number() Over (Order By tokenOrder) as tokenOrder.

  5. Select from the @taken table where the new tokenOrder is equal to the ID from the new inserted row in TableA

  6. Update TableA with timeToken and dateToken Where id is equal to the ID from the new inserted row in TableA

  7. Update TableB timeToken and dateToken as taken where tokenOrder is equal to the ID from the new inserted row in TableA

My problem is that this code is in a stored procedure and I put the call to the stored procedure in a loop, looping through 200 times and when I run it, I get the exact number of rows I am expecting, however the results are not right.

My Table B Data looks like this

dateToken    timeToken     tokenOrder taken
Monday  1:00pm   1     0
Monday  1:10pm   2     0
Monday  1:20pm   3     0

and so on all the way till midnight increasing by 10 minutes. (there is like a 3 hour break somewhere in there)

the results I get when I run my stored procedure are

dateToken    timeToken
Monday  1:10pm
Monday  1:30pm
Monday  1:50pm
Monday  2:10pm
Monday  2:30pm

So it appears to be skipping every other timeToken and I have no idea why

Here is my code:

@dateToken nvarchar(MAX) OUTPUT,
@timeToken nvarchar(MAX) OUTPUT,


Declare @TableA_PK BIGINT

DECLARE @taken table(  
        id int NOT NULL,  
        dateToken nvarchar(max),
        timeToken nvarchar(max),
        tokenOrder int,
        taken bit);

INSERT INTO TableA (dateToken, timeToken) VALUES (‘’, ‘’)

SET @TableA_PK=SCOPE_IDENTITY()

INSERT INTO @taken SELECT id, dateToken, timeToken, Row_Number() Over (Order By tokenOrder) As tokenOrder, taken FROM TableB WHERE taken = 0

SELECT @dateToken = dateToken, @timeToken = timeToken FROM @taken WHERE tokenOrder = @TableA_PK

UPDATE TableA SET dateToken = @dateToken, timeToken = @timeToken WHERE id = @TableA_PK

UPDATE TableB SET taken = 1 WHERE tokenOrder = @TableA_PK

Any help would be much appreciated. PLEASE HELP, i've been struggling with this for daysssss

One other thing, when I goto look at the data in TableB after I run this code, all the rows are marked as taken, which is expected.

Upvotes: 2

Views: 56

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

After inserting "TableA" identity value of it (@TableA_PK=SCOPE_IDENTITY()) increases by one.

But you are calculating tokenOrder value with "Row_Number() Over (Order By tokenOrder)" and only for "taken=0" rows. Every time this starts from "1".

That's why this code skips previously rows every run.

For example; In the first turn, you are getting the correct result. But in the second turn, you only take "token = 2" rows and skip "token = 1".

Upvotes: 0

Related Questions