CODEWITHSUNDEEP
sql-serverdelphistored-procedures
Niyoko
Niyoko

Reputation: 7672

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

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.

Upvotes: 0

Views: 3175

Answers (1)

GolezTrol
GolezTrol

Reputation: 116180

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.

Upvotes: 2

Related Questions