0

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.

Improve this question
0

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The TableA Primary Key is an auto-incrementing int. Sorry for leaving that part out. I've read that the scope_identity's numeric result can be assigned to an int parameter.
@theog: script the table, add to your question please. Add check this where the question was sure of datatypes... stackoverflow.com/q/6414700/27535
1

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

Improve this answer

1 Comment

Well, hell's bells, even if this doesn't pinpoint my issue, finding out about the little stackexchange data analyzer tool is worth it! I didn't know that was there--very cool.
1

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.

Improve this answer

1 Comment

I did look for triggers, but good point. Also a good idea on the print statement for troubleshooting. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.