CODEWITHSUNDEEP
sql-server-2008insertscope-identity
theog
theog

Reputation: 1064

SQL Server Scope_Identity() Usage Problem

I can't figure out why this multi-table insert (stored procedure) using scope_identity() is not working. This is the gist of it (forgive the unimaginative naming here, please):

enter image description here

TableB's RowID column is obviously an int, not set as identity auto-increment or anything like that.

If I run it in management studio, it says 1 row was affected, but it does not do the inserts.

If I run it from code I get an error saying that I can't insert a NULL value in TableB, RowID, which is correct, it has a not null constraint. But it should be getting the scope_identity() from the first table.

I've tried it in and out of transactions and with various parameter initializations... It's killing me. Thanks in advance for any assistance.

Upvotes: 0

Views: 2987

Answers (3)

HLGEM
HLGEM

Reputation: 96572

One other thing to look at is whether you have a trigger (especially an INSTEAD OF Trigger)on the table which might be causing the record to not get inserted.

You can add a print statement to see the value of the @newId variable after you have initialized it to see if it is null or if it has a value.

Try using explicit transactions and a try catch block and then you can see what error you are getting in the catch block if it doesn't make it past the first insert.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Nothing wrong with the code you have showed here. I think you have removed the problematic code when you simplified your sample.

Try this.

declare @TableA table (IDA int identity primary key, ColA int)
declare @TableB table (IDB int primary key, ColB int)

declare @RecordID int = 0,
        @Val1 int
        
if @RecordID = 0
  begin
    declare @NewID int

    insert into @TableA values (@Val1)
    set @NewID = scope_identity()

    insert into @TableB values (@NewID, @Val1)
  end
else
  begin
    print 'Do something else'
  end  

select *
from @TableA, @TableB

Result:

IDA         ColA        IDB         ColB
----------- ----------- ----------- -----------
1           NULL        1           NULL

Take it for a spin yourself. https://data.stackexchange.com/stackoverflow/q/103697/using-scope-identity

Upvotes: 1

gbn
gbn

Reputation: 432261

I assume tableA has an IDENTITY set on a number column?

SCOPE_IDENTITY() will be NULL if there isn't

The "1 row affected" isn't from this INSERT into tableA either.

Upvotes: 1

Related Questions