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.
Advertisement
Answer
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.