How to get the next identity value from SQL Server

Rather than asking "why the heck would you want to do this???", I'm going to assume you have a good reason.

Here's what I did for some quick-and-dirty test SQL, which worked fine in all but one case, even after emptying a previously-populated table:

    DECLARE @nextId INTEGER = IDENT_CURRENT('myTable')
    IF (SELECT COUNT(*) FROM myTable) > 0
    OR @nextId > 1
        SET @nextId += 1

I then used this to build a status string that I put into myTable (I wanted it to have the NEW identity value that the row would be given) to verify the SQL was working as expected.

The only case this does not handle properly is:

1. Create a new (empty) table and run the above code. It will correctly tell you that it will have an ID of 1.
2. Add a single element to the table. This will have an ID of 1, as predicted.
3. Empty the table.
4. Run the above SQL again. It will incorrectly tell you that the next entry will also have an ID of 1.

This is the one case for which I couldn't find a workaround. I was OK with it, since step 1 of my test involved adding more than one row to the table, but be aware of this one unlikely-but-possible sequence of events that could lead to an error.

There may be a "better" way, but it wasn't for production code, so quick-and-dirty was fine.

I can contribute another situation where knowing the next value would be useful. I have data in a line in Excel, such as: A B C X1 X2 Y1 Y2 Z1 Z2 I want to put A B C in one database table, and use the new primary key for that record in another table where I'm putting X1 and X2, then Y1 and Y2, then Z1 and Z2. So this one line would generate one record in the first table: xxx A B C

And three records in the second table

yyy xxx X1 X2

yyy+1 xxx Y1 Y2

yyy+2 xxx Z1 Z3

If I could determine xxx before starting the process, I could just increment it for each record being processed. I too made the mistake of just finding the highest value M and assuming M+1 would be the next one. That wasn't working because some uploads didn't complete due to errors, the transaction was rolled back, but the indexed PK's had been assigned and weren't going to be reused. Manual deletion of the highest valued PK records would cause a similar issue.

My simplistic solution was just to write an SP that gets the max value of the PK after uploading the first case.

CREATE     PROCEDURE [MIRR].[GetLast_CasesIndexID]
@GroupNumber as int Output
AS
BEGIN
    SELECT @GroupNumber = max(CasesIndexID)  
    FROM MIRR.Cases
END
GO