T-SQL SCOPE_IDENTITY() in Stored Procedure

clearz

I am writing a stored procedure in T-SQL where I need to get the last id of a table where I do an INSERT.

If I do something like the following

CREATE PROCEDURE procedure1
    
AS
BEGIN
    declare @Pos int
    INSERT INTO test (col1) values ('TTT');
    set @pos = SCOPE_IDENTITY();
    print @pos
    SELECT SCOPE_IDENTITY();
END
GO

@pos will print out as something like 143 while the SELECT will return 144 which is the correct id of the last inserted record. If I had 10 insert statements then @pos will be 143 and the SELECT will return 153. Why is the incorrect value being stored in @pos as I need it to be stored in a variable to access it in the stored procedure. I am using MSSQL 2000

Kernel32

Select @pos = SCOPE_IDENTITY()

clearz

Hi kernel, I tried that method with the same result. The strange thing is both methods work even though they print out a different value than the accual one. Take for example the following procedure. If the highest value in the identity column in the table 'test' had a value of 17 before running it, it will print out 18 18 and will return 19. The table 'test' will have a new row 'Bar' in col1 and 19 in the identity column.

CREATE PROCEDURE procedure1
       
AS
BEGIN
    DECLARE @pos int;
    
    INSERT INTO test (col1) VALUES ('Foo');
    
    SET @pos = SCOPE_IDENTITY();
    PRINT @pos;
    UPDATE test SET col1='Bar' WHERE id = @pos;
    SELECT @pos = SCOPE_IDENTITY();
    PRINT @pos;
    SELECT SCOPE_IDENTITY();

END

bonkey

How are you testing your return value?

More importantly...in your second example, if the UPDATE is working, then @POS is set to the correct value (18 in your example).

Why not change your final select to be SELECT @Pos and see if that works.

clearz

Bonkey I use SQL Manager 2005 to test the procedures. It displays a table with the returned data In this case one cell with the value 19 in it.

I tried putting SELECT @pos at the end and it indeed returns 19. I find this behaviour strange. Why is it printing 18 inside the procedure? and even updates the correct row when that rows id is 19. I read that the scope_identity dosen't get updated until after the procedure has executed but this still dosent explain how it can update the correct row while containing the wrong value.