0
How to get the id that has just been inserted into the bank on the server side, use Delphi XE10.1 Sqlserver Bank and Firedac, I would like to take the id to use in the child table when it is master detail. as example below
procedure TsrmCadastro.dspPessoaBeforeUpdateRecord(Sender: TObject;
SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;var Applied: Boolean);
var idPessoa : Integer;
begin
if UpdateKind = ukInsert then
begin
if SourceDS = qryPessoa then
Begin
idPessoa := ????????;
End
Else if SourceDS = qryPessoaJuridica then
Begin
DeltaDS.FieldByName('pessoa_juridica_id').NewValue := idPessoa;
End;
end;
You can use
SELECT SCOPE_IDENTITY()
in SQL Server 2008+– Not The Real Hemingway
I used as example below but returns Zero this code put in place of ??????? 

 qry := TFDQuery.Create(self);
 qry.Connection := dm.Conn;
 try
 qry.SQL.Add('SELECT SCOPE_IDENTITY() AS ID');
 qry.Open();
 idPessoa := qry.FieldByName('ID'). Asinteger; Finally Freeandnil(qry); end;
– Ailton Branco
You have to run SCOPE_IDENTITY() immediately after INSERT.
INSERT INTO dbo.tabela(nome) VALUES('Nome Sobrenome'); SELECT SCOPE_IDENTITY();
– Not The Real Hemingway