Scope_identity is giving null values?

Question

I have stored procedure in which I use scope_identity()

Create table table1
    id int,
    name varchar,
    age int 

Create procedure details
    @id int,
    @name varchar,
    age int
    
    Select @old_id = id , @name =  name ,@age = age
    from table1 
    where id = @id
    
    if @old_id is null
    begin
        insert into table1(id, name , age)
        Select scope_identity(), @name, @age
    end

Error I get:

cannot insert null into id column, column does not allow null, insert fail

Any idea how to resolve this? Any help is appreciated.

Answer 1

I'm going to answer the why you're getting a NULL here, though it is unlikely that this answer will answer the qusetion you are really asking; of course I can't answer that question and you've never told us what that question is.

As explained in all the comments, SCOPE_IDENTITY returns the value of the last IDENTITY value generated within the current scope. To quote the documentation:

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

In your procedure there is no prior INSERT, so there is not prior generated value, thus SCOPE_IDENTITY can only have the value NULL. This, as a result, means that the INSERT statement fails as your column id (which I assume is actually a foreign key) cannot be NULL.

A normal statement with the use of SCOPE_IDENTITY() would look something like this:

INSERT INTO dbo.SomeTable (SomeColumn) --SomeTable has a column with the IDENTITY property
VALUES(@SomeValue);

INSERT INTO dbo.AnotherTable(ForeignKey, AnotherColumn)
VALUES(SCOPE_IDENTITY(), @AnotherValue);

Note, as well, that you define your column/parameter name as a varchar(1). I would suggest fixing that as there are very few people, if any, who have a single character for their name.