Page 1 of 1
Parameters of a stored proc by name
Posted: Wed 20 Apr 2011 19:23
by pickepique
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
Posted: Thu 21 Apr 2011 10:43
by AndreyZ
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.
Posted: Fri 22 Apr 2011 09:44
by pickepique
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
Posted: Fri 22 Apr 2011 11:45
by AndreyZ
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;