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.

Which dbms are you using? (This is a product specific question.) — jarlh, Apr 09 '21 at 08:55
`SCOPE_IDENTITY` returns the value of last `IDENTITY` value generated in the current scope. What is the statement *prior* to the above `INSERT`? I assume the table you are inserting into prior to that has a column with the `IDENTITY` property? — Thom A, Apr 09 '21 at 09:06
its an select statement which take the records of table1 after that this insert statement is there — jitender singh, Apr 09 '21 at 09:09
The `SELECT` (in your question) is the values you are inserting into `table1`, @jitendersingh ; I'm asking what the statement prior to your `INSERT` is, as that's likely the statement that would be causing `SCOPE_IDENTITY` to have a `NULL` value. — Thom A, Apr 09 '21 at 09:10
There's no prior `INSERT` in the scope prior to the `INSERT` into the table `table1`, so yes, `SCOPE_IDENTITY` *will* have the value `NULL`. What are you trying to achieve with `SCOPE_IDENTITY` here? — Thom A, Apr 09 '21 at 09:16
Is `table1` the table you want to have the auto-incrementing `id` value? — Stu, Apr 09 '21 at 09:16
I want to add unique id by using scope_identity which is linked to previous id's — jitender singh, Apr 09 '21 at 09:17
Why not use an `IDENTITY` and have SQL Server handle the value, rather than you pass one to the data engine? — Thom A, Apr 09 '21 at 09:18
What do you mean by previous - there is not previous insert in your code above? — Stu, Apr 09 '21 at 09:19
@larnu its an business thing, and they want scope_identity to be used — jitender singh, Apr 09 '21 at 09:22
So in your example above, you have already inserted into a table which you haven't shown us and you want to use the identity value from that? — Stu, Apr 09 '21 at 09:23
Share with us tghe requirement you have *actually* been given. `SCOPE_IDENTITY` will always return `NULL` if you haven't previously performed an `INSERT`. — Thom A, Apr 09 '21 at 09:24
*"should I add screenshot of that table? "* no, that's no good to us. Give us the whole code and the requirement you have... — Thom A, Apr 09 '21 at 09:25
I cant share code due to security reasons, I am sorry for that — jitender singh, Apr 09 '21 at 09:32
If you won't share the requirement you have been given, nor the a reasonable representation of the code you have then we can't help you, @jitendersingh , but there is no security concerns in sharing psuedo code... — Thom A, Apr 09 '21 at 09:48

score 0 · Accepted Answer · answered Apr 09 '21 at 10:30

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.

there's another possibity - if an insert into `SomeTable` is done, but then other inserts are done into a non-identity table before the use of `scope_identity`, it will also be `null` - it should be saved into local variable immediately following the insert. — Stu, Apr 09 '21 at 11:02
Though true, @Stu , I was simply demonstrating a `INSERT` preceded immediately by another `INSERT`. As we never got the *real* question from the OP, I felt it someone pointless to go completely in depth about the ins and outs of the function; they have a link to the documentation in the answer if they really want to read up on it's full behaviour. — Thom A, Apr 09 '21 at 11:07

Scope_identity is giving null values?

1 Answers1