Why does this stored procedure not return the correct ID?

Question

Intermittently this stored procedure will return an ID that is in the millions when it should actually only be around 400.

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestStoredProcedure]
(
    @Title VARCHAR(50),
    @ID INT OUTPUT
)
AS

DECLARE @ResultsTable Table(InsertedID INT);

INSERT INTO Table 
(
    Title
)
OUTPUT INSERTED.ID INTO @ResultsTable
VALUES 
(
    @Title
);
SELECT @ID = (SELECT TOP 1 InsertedID FROM @ResultsTable);

This stored procedure used to return the ID by using SCOPE_IDENTITY() but that had the same issue.

There are no triggers in the database. There are only the indexes on the primary keys that SQL Server creates automatically. There are no tables at all that have an ID anywhere near as large as what is being returned so I have no idea where these large numbers are coming from.

Any help or suggestions would be appreciated.

Edit: As I said above, this stored procedure originally used SCOPE_IDENTITY() like so:

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestStoredProcedure]
(
    @Title VARCHAR(50),
    @ID INT OUTPUT
)
AS

INSERT INTO Table 
(
    Title
)
VALUES 
(
    @Title
);
SELECT @ID = SCOPE_IDENTITY();

Edit 2:
The exact SQL version is:
Microsoft SQL Server 2005 - 9.00.4230.00 (X64) Jul 30 2009 13:42:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Edit 3:
This is how the stored procedure is being called (good old classic ASP, it's a legacy site)

set obj_CMD = Server.CreateObject("ADODB.Command")
    with obj_CMD
        .ActiveConnection = Application("DSN")
        .CommandText = "TestStoredProcedure"
        .CommandType = adCmdStoredProc

        dim txt_title
        txt_title = "Some text"

        set oParam = .CreateParameter("@Title",adVarChar,adParamInput,50,txt_title)
        .Parameters.Append oParam       
        set oParam = .CreateParameter("@ID",adInteger,adParamOutput)
        .Parameters.Append oParam
        .Execute

        dim ID
        ID = .Parameters("@ID").Value

    end with
set obj_CMD = nothing

Edit 4:
Running DBCC CHECKIDENT ('Table') returns:

Checking identity information: current identity value '422', current column value '422'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is as expected.

Answer 1

My understanding is that it is not possible to determine the value assigned to an identity column until after the row has been created, and that includes trying to access it via the OUTPUT clause.

@@identity and scope_identity() really should work. Can you post your samples using that code?

(A side-note: when I say "my understanding is...", I mean that I've looked into this a lot over several versions of SQL Server, and I've never found a way to do this, and moreover I seem to recall reading a few posts and articles over the years that say it flat-out cannot be done, but hey, I would really like to be wrong, so I'll watch this post.)

Answer 2

I think the problem is how you execute your sp.

declare @i int = 0

EXEC [TestStoredProcedure] '1', @i OUTPUT

select @i

Maybe you forget the OUTPUT keyword...

EDIT: And if it so - use SCOPE_IDENTITY() because this is better way to get the id value in your case.