A better way of passing parameters to a TADOStoredProc (Delphi)

Question

I am needing to convert a large amount of SQL queries into stored procedures. I have some code that updates about 20 or 30 values at one time in one Delphi procedure. I can handle creating a stored procedures to do such a thing. The problem is my way to pass parameters to stored procedures is very bulky like this:

    with stored_procedure do......
    Param := Parameters.AddParameter;
    Param.Name := '@SSN';
    Param.Value := edtSSN.text;

    Param := Parameters.AddParameter;
    Param.Name := '@FirstName';
    Param.Value := edtFirstName.Text;

    Param := Parameters.AddParameter;
    Param.Name := '@LastName';
    Param.Value := edtLastName.Text;

    Param := Parameters.AddParameter;
    Param.Name := '@UserRID';
    Param.Value:= GetRIDFromCombo(cbUser);

I also am not sure if that causes a memory leak(is it necessary to free such TParameter objects?)

Anyone have a better way of handling a large amount of parameters? (I can not use a new library. I must use ADO, and the SQL I use is MSSQL) (also, I'm NOT using ADO.net)

Solution 1

This doesn't cause a memory leak. stored_procedure will clean up its parameters. You can confirm this with FastMM by adding the following to your .dpr:

  ReportMemoryLeaksOnShutdown := True;

First, I'd get rid of the "with" statement. It can lead to more problems and less readable code.

I'd create a helper method that accepts a stored procedure, a parameter name and a parameter value, which will make your code more manageable.

AddParam(stored_procedure, '@SSN', edtSSN.text);
AddParam(stored_procedure, '@FirstName', edtFirstName.Text);
AddParam(stored_procedure, '@LastName', edtLastName.Text);
AddParam(stored_procedure, '@UserRID', GetRIDFromCombo(cbUser));

Solution 2

There's an accepted answer :-), but I want to point you to simpler and easier way to define and use the parameters with one line :

stored_procedure.Parameters.CreateParameter('SSN',ftString,pdInput,30,edtSSN.text);

It's simple and flexible, because you can define the input and output parameters with same line.

and from Delphi help:

function CreateParameter(const Name: WideString; DataType: TDataType;
    Direction: TParameterDirection; Size: Integer; 
    Value: OleVariant): TParameter;

Solution 3

ADO will create the parameters for you, you just need to call Refresh on the parameters object:

 SP.Connection := SqlConnection; // must be done before setting procedure name
 sp.ProcedureName := 'MyStoredProc';
 sp.Parameters.Refresh; // This will create the parameters for you as defined in SQL Server
 sp.Parameters.ParamByName('@SSN'').Value  := SSN; // params now exist

etc

If any parameters are output you will need to set them explicitly:

   sp.Parameters.ParamByName('@ReturnValue').Direction := pdInputOutput;

Comments

[yellahd]

I didn't know that existed. Thanks for sharing!

[benwills]

Thanks Bruce & Rob, That's the beauty of SO, I have learned many things for Delphier here, and trying to share what I know with others :-)

[Earlz]

That is quite handy, though I still prefer the first way as it is simpler for a large amount of only input parameters

[DiGi]

This is nice but it is working only if procedure is in connection's default database. There is a bug in ADO that destroy parameters if you use DifferentDatabase.Owner.ProcedureName

[Tony]

I have also found that .Refresh does not always work, even within the same database.owner. I have not found any pattern to when it works and when it doesn't, but when it doesn't I get and error like "Parameter '@SearchText' not found." when I use .ParamByName. That same call works 99.9% of the time. Adding params manually seems to bypass the problem.

[Kiril Hadjiev]

Thank you very much. I was having troubles with parameters of type TDate and TDateTime, because sp.parameters.refresh creates parameters with dataType of WideString instead of TDate.