[DUG] Retrieving the Last Inserted ID from MS SQL using ADO

[Neven MacEwan]

Leigh

There is a gotcha here and that is that since mssql 7 stored procs are not
precompiled (I assume this is because with the addition of user defined 
functions & aggregates they
can get better overall perf by reopt the querys as a whole). I don't 
know if this is an
SQL Express edition restriction but I've had nasty things happen where 
you execute
seemily the same SP a number of times and 1 execution takes 10 seconds+ 
and the rest
are instantaneous. This is a killer on web sites. So I'm moving away 
from SP's unless
required. One of the ways of course to mitigate this is make your stored 
procs trivial but
that has the side effect of making them numerous.

Hi/Low key gen is a lot more deterministic IMHO

Neven
> I agree with John
>
> I am using store procedure to insert record and select id
>
> 		SELECT @Id = (SELECT SCOPE_IDENTITY())
>
> 		SELECT @Id
>
> Regards
> Leigh
> www.smootharm.com
>
> -----Original Message-----
> From: delphi-bounces at listserver.123.net.nz
> [mailto:delphi-bounces at listserver.123.net.nz]On Behalf Of John Davys
> Sent: Tuesday, March 11, 2008 2:08 PM
> To: 'NZ Borland Developers Group - Delphi List'
> Subject: RE: [DUG] Retrieving the Last Inserted ID from MS SQL using ADO
>
>
> If you do a query to do the insert and then do another query to get the
> result of the scope_insert() then these do not execute within the same sql
> server scope (batch) i.e. the query doing the scope_identity() call does not
> see the id created from the previous insert (see SQL Server Books Online for
> the SCOPE_IDENTITY() function).
>
> I suggest a better approach would be to use a stored procedure to do the
> insert and then return the new id as an output parameter from the stored
> proc.
>
> Regards
> John
>
> -----Original Message-----
> From: delphi-bounces at listserver.123.net.nz
> [mailto:delphi-bounces at listserver.123.net.nz] On Behalf Of
> mstokes at semantic.co.nz
> Sent: Tuesday, 11 March 2008 12:29 p.m.
> To: delphi at listserver.123.net.nz
> Subject: [DUG] Retrieving the Last Inserted ID from MS SQL using ADO
>
>
> Hello Everyone,
>
> I am having a few issues trying to return the last inserted id after
> executing an insert statement using the TADOQuery component.
>
> I can retrieve this value with ease by running the following SQL statement
> when using the SQL Query Analyzer which is part of the the SQL Server
> Enterprise Manager tool.
>
>
>   function GetNewID : integer;
>   begin
>     with TADOQuery.Create(nil) do try
>       Connection := dm.conFARST;
>       SQL.Add('select scope_identity() as ''newid''');
>       Open;
>       Result := FieldByName('newid').AsInteger;
>     finally
>       free;
>     end;
>   end;
>
> The problem that I am having is that when I execute this query within the
> ADO query component, it always returns the value of 0.
> The issue seems to be with the Delphi 7 ADO components.
>
>
> Has anyone come across this issue before?
> Any feedback would be appreciated.
>
>
> Cheers,
>
> Mike Stokes
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at listserver.123.net.nz with
> Subject: unsubscribe
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at listserver.123.net.nz with
> Subject: unsubscribe
>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at listserver.123.net.nz with Subject: unsubscribe
>
>
>