Board index » delphi » Stored procedure and parameters (SQL Server, D5)

Stored procedure and parameters (SQL Server, D5)

In old system (SQL Anywhere) I have about 200 stored procedures (most of
them have parameters).
Now I have to move it to SQL Server. I do not want to put 200 ADOStoredProc
components on my form. What is the easiest way to create stored procedure
parameters?

What I would like to have is:

with myProc do begin
  Parameters.Clear;
  ProcedureName:='myProc';
  MagicCommand;    // Create new set of parameters
  Parameters.ParamByName('A').Value:='A';
  ...
  ...
  ExecProc;
end;

MH

 

Re:Stored procedure and parameters (SQL Server, D5)


Quote
>What I would like to have is:

>with myProc do begin
>  Parameters.Clear;
>  ProcedureName:='myProc';
>  MagicCommand;    // Create new set of parameters
>  Parameters.ParamByName('A').Value:='A';

If the stored procedure returns a dataset you want to use a TadoDataset
component.  If there is no dataset use a TadoCommand

With TadoDataset1 Do begin
  commandType := cmdStoredProc;
  CommandText := 'myProc'
  parameters.refresh;
 Parameters.ParamByName('A').Value:='A';
end;
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Stored procedure and parameters (SQL Server, D5)


Thank you Brian.

Yes, the MagicCommand is Parameters.Refresh. I tried it before sending my
message to NG but it did not work. I forgot that with SQL Server I have to
add '@' to each parameter name and this was my real problem.
I think I had too much coffee.

Marius

Re:Stored procedure and parameters (SQL Server, D5)


Here's some of my code.

          // Update Reconciliation Status table
          spUpdate.Connection := CnCamraExtn;
          spUpdate.ProcedureName := 'UpdateReconStatusPeriod';
          spUpdate.Parameters.Refresh;

          spUpdate.Parameters.ParamByName('@Account').Value :=
            FChangedPeriodList.FieldByName ('Account').AsString;

          dtTemp := EncodeDate (FChangedPeriodList.FieldByName
('Year').AsInteger,
                                FChangedPeriodlist.FieldByName
('Month').AsInteger,
                                1);
          dtTemp := IncMonth (dtTemp, 1);
          dtTemp := dtTemp - 1;

          spUpdate.Parameters.ParamByName('@Period').Value := dtTemp;
          spUpdate.Parameters.ParamByName('@Status').Value :=
            FChangedPeriodList.FieldByName ('Reconciled').AsBoolean;
          spUpdate.Parameters.ParamByName('@ChangeDate').Value := Now;
          spUpdate.Parameters.ParamByName('@Author').Value :=
ProgramOptions.UserName;

          spUpdate.ExecProc;

Quote
"Marius Horak" <marius.ho...@tnsofres.com> wrote in message

news:3cceb516$1_1@dnews...
Quote
> In old system (SQL Anywhere) I have about 200 stored procedures (most of
> them have parameters).
> Now I have to move it to SQL Server. I do not want to put 200
ADOStoredProc
> components on my form. What is the easiest way to create stored procedure
> parameters?

> What I would like to have is:

> with myProc do begin
>   Parameters.Clear;
>   ProcedureName:='myProc';
>   MagicCommand;    // Create new set of parameters
>   Parameters.ParamByName('A').Value:='A';
>   ...
>   ...
>   ExecProc;
> end;

> MH

Re:Stored procedure and parameters (SQL Server, D5)


On Tue, 30 Apr 2002 16:15:24 +0100, "Marius Horak"

Quote
<marius.ho...@tnsofres.com> wrote:
>Now I have to move it to SQL Server. I do not want to put 200 ADOStoredProc
>components on my form. What is the easiest way to create stored procedure
>parameters?

I don't use the stored procedure component atall for non-interactive
stuff, I just build the stored procedure command line and execute it.
Because single quotes are needed for the parameters, the command line
needs to be built carefully, I have a set of functions to quote
strings, convert blanks to NULL, create ISO dates, etc.

cmd  = EXEC mig_find_booking 'RML-0004',2019,'2002-04-24T00:00:09'

DataSet.Recordset := ADOConn.Execute (cmd) ;

Angus

Other Threads