Parameters of a stored proc by name

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
pickepique
Posts: 6
Joined: Thu 05 Mar 2009 18:05

Parameters of a stored proc by name

Post by pickepique » Wed 20 Apr 2011 19:23

Hello
Is there anyway to call a stored procedure in a form
{:RETURN_VALUE = CALL MyProc;1(
@Param1 = :Param1,
@Param2 = :Param2
)
}
I try thing same as above in a update statement of a MSQuery but that don't work.
Actually i want to call a stored proc with its parameters by name, not by the order they are declared
However SQLserver allows this synthax

Regards
Bruno Petit

AndreyZ

Post by AndreyZ » Thu 21 Apr 2011 10:43

Hello,

Please try using the EXECUTE statement in the MSQuery.SQLUpdate property in the following way:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
begin
  MSQuery.SQL.Text := 'select * from dept'; // the dept table has three fields: DEPTNO INT, DNAME VARCHAR, LOC VARCHAR
  MSQuery.SQLUpdate.Text := 'EXECUTE test.dbo.Dept_Update @DEPTNO = :DEPTNO, @DNAME = :DNAME, @LOC = :LOC'; // the Dept_Update stored procedure has the following definition: UPDATE DEPT SET DNAME = @DNAME, LOC = @LOC WHERE DEPTNO = @DEPTNO;
  MSQuery.Options.StrictUpdate := False;
  MSQuery.Open;
end;
You should set the MSQuery.Options.StrictUpdate option to True to avoid the "Update failed" error message. In this case SDAC cannot determine how many records were changed, and that's why it shows this error message. For more information, please read the SDAC documentation.
Last edited by AndreyZ on Fri 22 Apr 2011 09:55, edited 1 time in total.

pickepique
Posts: 6
Joined: Thu 05 Mar 2009 18:05

Post by pickepique » Fri 22 Apr 2011 09:44

Thanks for the reply

This time it works indeed, but i can't use the help of a usual :RETURN_VALUE ptResult parameter, something like the following

Code: Select all

MSQuery1.SQLUpdate.Text := 'EXECUTE :RETURN_VALUE = test.dbo.Dept_Update @DEPTNO = :DEPTNO, @DNAME = :DNAME, @LOC = :LOC'
don't work
How can i get a return value of a SP execute ?

Regards
Bruno

AndreyZ

Post by AndreyZ » Fri 22 Apr 2011 11:45

To get the return value of a stored procedure, you should use the BeforeUpdateExecute event handler in the following way:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject); 
begin 
  MSQuery.SQL.Text := 'select * from dept';
  MSQuery.SQLUpdate.Text := 'EXECUTE :RETURN_VALUE = test.dbo.Dept_Update @DEPTNO = :DEPTNO, @DNAME = :DNAME, @LOC = :LOC';
  MSQuery.Options.StrictUpdate := False; 
  MSQuery.Open; 
end;

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if stUpdate in StatementTypes then begin
    Params.ParamByName('RETURN_VALUE').ParamType := ptOutput;
    Params.ParamByName('RETURN_VALUE').DataType := ftInteger;
  end;
end;

Post Reply