Passing parameter values from Delphi to a SQL Server stored procedures by name

Question

My question is same as Shaun's (link)

But, that question seem to have no answer.

Is there tools other than TADOStoredProc that possible to be used to pass parameter by name?

I know it's possible by using Parameters.Refresh. But I dont want to use that because it makes additional round-trip to server.

I also have tried UniDAC, but it also not possible to pass parameter by name.

possible duplicate of [A better way of passing parameters to a TADOStoredProc (Delphi)](http://stackoverflow.com/questions/1159489/a-better-way-of-passing-parameters-to-a-tadostoredproc-delphi) — Gerry Coll, Sep 26 '12 at 07:39

GolezTrol · Accepted Answer · 2012-09-26T07:28:24.890

If you add the procedure name in design time, you can also check the parameters in design time (you'll need a connection, I think). That way, you don't have to put all the SQL in your code, and you don't have to check for parameters during runtime. Just put an ADOStoredProc on the ~~form~~ datamodule for each procedure you're going to call. You can give them a more sensible name, and you save a lot of code.

Same goes, of course, for queries and commands.

Alternatively, you can add the parameters from code yourself. You can specify the parameters, along with their name, type and other properties using YourADOStoredProc.Parameters.Add.

If you add the ADO controls to one or more datamodule, you can easily call them from the whole application. You can even write methods (and I think you should), to wrap the calls in. That way, you don't have to mess around with parameters throughout your application, and in that wrapper method, you can configure the parameters:

procedure TYourDataModule.DeleteCustomer(CustomerId: Integer);
var
  CustomerIdParam: TParameter;
begin
  with YourDeleteCustomerADOStoredProc do
  begin
    CustomerIdParam := Parameters.FindParam('P_CUSTOMERID');

    if CustomerIdParam = nil then
    begin
      CustomerIdParam := Parameters.AddParameter;
      CustomerIdParam.Name := 'P_CUSTOMERID';
      CustomerIdParam.DataType := ftInteger;
      CustomerIdParam.Direction := pdInput;
      //CustomerIdParam.Size := 40; // May be needed for strings;
    end;

    CustomerIdParam.Value := CustomerId;

    ExecProc;

  end;
end;

That way, you can just call YourDataModule.DeleteCustomer(20) throughout the application, without having to worry about parameters. But as you can see, it requires a little coding, so you could reconsider using the design time configuration. It's really easier.

It's not option. Because I use so many stored procedure in single form, and it make form designer look ugly. And I want to add parameter at run-time. — Niyoko, Sep 26 '12 at 07:11
You should add them to a datamodule, especially if you got so many. You can even create multiple datamodules so you can group them. Note that a datamodule is not an actual form. It's just an aid to save you a lot of coding. It doesn't cost a window handle in runtime, and it's very lightweight, so you don't have to be afraid to use a couple of those. — GolezTrol, Sep 26 '12 at 07:13
I would advise you to use a datamodule anyway, and wrap the code to call the stored proc in a method in that datamodule. That way, you can choose design time or runtime configuration, or even choose to put the SQL in your Delphi code if you have to. Also, you can later choose to use commands instead of stored procs, or change the database you used altogether. I've added a snippet showing how you can add parameters in runtime from the code. Of course you don't have to check them on every call, you could also use the OnCreate event of the DataModule to initialize them all at once. — GolezTrol, Sep 26 '12 at 07:31

Passing parameter values from Delphi to a SQL Server stored procedures by name

1 Answers1