>>>>>I can see that your SP is the same as mine; so this eliminates problem with stored procedure. But the way you call the stored procedure is different. First you declare the variable "out"; I didn't. But you didn't declare "in" and I did.
>>>>
>>>>I could have declared @in also - I just used inline declaration.
>>>
>>>I know. I think the issue has something to do with the aliased column name that I use in my code. Could it be that SQL Server sees it as a constant?
>>
>>Post some of your CA definition in order to figure this problem. BTW, is it the only way to refresh scope_identity()? This one is too tricky for me.
>
>I am not sure what you mean by CA definition though. I think the problem is with how the SP is called. BTW, I found that a simpler way to get the newly added identity value is to simply insert a record to the cursor, call tableupdate(), and then call a SQL Select that "select scope_identity() as NEW_PK". It works but maybe I don't see the downside of calling it "after" record is inserted. Even though I do it immediately after insert, which is pretty much what the code with stored procedure does.
That way is not reliable. The whole thing should be done as one transaction.
Try instead
declare @Output table(ID int)
insert into myTable (Field1, Field2) output Inserted.ID into @Output values (@Value1, @Value2)
select ID from @Output
If it's not broken, fix it until it is.
My Blog