Call stored procedures.
>Hi,
>
>SQL 2K offers 3 identity retrieving functions: @@IDENTITY, IDENT_CURRENT, and SCOPE_IDENTITY
>
>My data-handling objects use VFP cursors and TableUpdate() to save records - one at a time. If the record is new - I want to retrieve the identity value for the new record. I have been using @@identity but it is useless if you have cascading-insert triggers on the table you insert into.
>
>I'd use IDENT_CURRENT, but it is useless because it is not session-specific (I'd like to know what they were thinking on this one)
>
>so ... I am left with SCOPE_IDENTITY, which at first, seems to fit the bill - works great in SPT - however - the TableUpdate() function in VFP uses the SQL SP_ExecuteSQL stored procedure which is it's own "scope". Therefore - after the tableupdate insert, my scope is "done" and a SCOPE_IDENTITY call returns null.
>
>Any ideas how I can get back the identity from SQL for my original "inserted into" table when using tableupdate?
>
>TIA,