About RemoteDB TXDataset(stored procedure&#

Whether the TXDataset can execute the stored procedure and get the return value or the output parameter value?

1 Like

TXDataset can execute any SQL statement. The only difference is that a specific component for stored procedures are not provided, so you just need to build the SQL yourself and get the results using FieldByName. It depends on the underlying component you use server-side. For example, if using FireDac, you can execute stored procedures with TFDQuery (and thus with TXDataset): 

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Executing_Stored_Procedures_(FireDAC)#Using_TFDQuery
The stored procedure in MSSQLServer2008:

===================================================
create proc sp_test
@InVal varchar(100),
@OutVal varchar(200) output
as
begin
  select @OutVal='Hello' + @InVal;
  RETURN 0
end
GO

===================================================
// Execute the stored procedureand and get the 2nd OutPut value.
//The server-side uses a TFDQuery. Stored procedure execute OK, but the OUTPUT parameter can not return to the client(XDataset1). Possible??

var
  Params: TParams;
  AParam: TParam;

  Params := TParams.Create(nil);
  AParam := TParam.Create(Params, ptInputOutput);
  with AParam do
  begin
    Name := 'out';
    DataType := ftString;
  end;
  try
    XDataset1.Database.ExecSQL('EXEC sp_test ''This is input Value'', :out', Params);
    ShowMessage(Params.ParamByName('out').AsString);
  finally
    AParam.Free;
    Params.Free;
  end;

Use the following SQL statement:



DECLARE @out varchar(200)
EXEC sp_test ', World', @out OUTPUT
SELECT @out AS OUT


then just open the dataset and get the value using field by name:


XDataset1.Open;
OutValue := XDataset1.FieldByName('OUT').AsString;


1 Like

OK, Thanks!

Hi Wagner,

Is this also be possible with ElevateDB? I have an stored procedure with parameters in ElevateDB which I would execute similar to the described approach.

Hi Michael, that depends on the specific ElevateDB syntax. Doesn't ElevateDB provide an SQL statement that returns values from stored procedures in a dataset format?

According to information from ElevateDB manual.

You can only retrieve result sets from procedures from a client (TEDBStoredProc).

So it seems it isn't possible.