CODEWITHSUNDEEP
sqlsql-serverstored-procedures
JC6T
JC6T

Reputation: 165

Is it possible to get the last number generated from ROW_NUMBER in SQL

I currently have such a query inside my stored procedure: 

INSERT INTO YTDTRNI (TRCDE, PROD, WH, DESCR, UCOST, TCOST, DRAC, CRAC, REM, QTY, UM, ORDNO, TRDATE, SYSDATE, PERIOD, USERID)
    SELECT 
        'AJ', PROD, WH, DESCR, 0, -TCOST, STKGL, COSGL,
        'MASS ADJUSTMENT', 0, UM, 
        CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS nvarchar(255)), 
        GETDATE(), GETDATE(), @inputPeriod, @inputUserId
    FROM 
        INV
    WHERE 
        H = 0

I am making use of row_number() to get a number that is incrementing itself while executing the query.

For example the query above INSERT 2018 records in YTDTRNI table. So the last number generated by this row_number() function is 2018. My question now is whether is it possible to get hold of this last number generated by row_number().

In another table, I have a value stored as I1000 for example. So after performing the above operation. I need to update this table with the new value of I3018 (1000+2018).

I am stuck on how to move on. Open to any advice if whatever I am doing is incorrect or not following conventions/standards.

Upvotes: 0

Views: 190

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

@@ROWCOUNT is not reliable if there are triggers in the database. I would strongly discourage you from using it.

Instead, use OUTPUT:

declare @t table (rn int);

insert into . . .
    output (inserted.ordno) into @t
    select . . .;

Then you can simply do:

select max(ordno) from @t;

This captures exactly what is input into the table.

Upvotes: 0

Squirrel
Squirrel

Reputation: 24773

just do a @@rowcount after your query

DECLARE @rc INT

INSERT INTO YTDTRNI ( ... )

SELECT @rc = @@rowcount

after that you can use this @rc to update the other table

Upvotes: 2

Related Questions