Hi,
I am using Lazarus 1.6 FPC 3.0.0 for x86_64-win64-win32/win64. I need to be able to execute a stored procedure in SQL Server and then read the return integer value, all output parameter values and the records returned by the stored procedure. I am using TODBCConnection with TSQLQuery.
The below code illustrates the scenario where the stored procedure named sp_Test takes one output varchar parameter, one input varchar parameter and returns an integer.
with query.SqlQuery do
begin
DataBase:= connSet.Connection;
PacketRecords:= -1;
UsePrimaryKeyAsKey:= false;
SQL.Clear;
SQL.Text:= 'EXEC :Return = Stocktake.dbo.sp_Test :TestOut OUT, :TestIn';
Params.BeginUpdate;
Params.CreateParam(TFieldType.ftInteger, 'Return', TParamType.ptResult);
Params.CreateParam(TFieldType.ftString, 'TestOut', TParamType.ptOutput);
Params.CreateParam(TFieldType.ftString, 'TestIn', TParamType.ptInput);
Params.ParamByName('TestIn').Value:= 'TEST IN';
Params.EndUpdate;
ReadOnly:= true;
Open;
ShowMessage('TestIn: ' + Params.ParamByName('TestIn').AsString);
ShowMessage('TestOut: ' + Params.ParamByName('TestOut').AsString);
ShowMessage('Return: ' + Params.ParamByName('Return').AsString);
end;
The stored procedure executes correctly using the value of the input parameter and I am getting the records returned okay which can be loaded into a DBGrid.
What I am not getting, however, is a returned value for the output parameter or a value for the return integer as set by the stored procedure. I am trying to do this by reading the query parameter values after opening the records, but perhaps that is not the right way to do it? I know the parameters are going into SQL Server because I can read the values back through the records returned. Note that in the actual requirement I need to be able to read the output parameter values and the returned integer separately from the records returned because there will not always be records returned and I need to take action based on the specific return integer and output values first.
Any assistance would be greatly appreciated...
Thanks