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)

score 20 · Answer 1 · edited Jul 21 '09 at 21:43

20

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;

edited Jul 21 '09 at 21:43

zendar

13,384
14
59
75

answered Jul 21 '09 at 15:08

Mohammed Nasman

10,992
7
43
68

2

This appears to be a better answer than mine. – Bruce McGee Jul 21 '09 at 15:21
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 :-) – Mohammed Nasman Jul 21 '09 at 17:41
That is quite handy, though I still prefer the first way as it is simpler for a large amount of only input parameters – Earlz Jul 21 '09 at 19:24

score 11 · Accepted Answer · answered Jul 21 '09 at 14:40

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));

score 11 · Answer 3 · answered Jul 21 '09 at 21:30

11

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;

answered Jul 21 '09 at 21:30

Gerry Coll

5,867
1
27
36

1

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 – DiGi May 17 '10 at 11:49
1

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. – Tony Aug 17 '11 at 21:56
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. – Kiril Hadjiev Aug 22 '18 at 11:09

score 0 · Answer 4 · answered Dec 15 '14 at 05:49

0

This is the shortest I know:

stored_procedure.Parameters.ParamByName('@SSN').Value := edtSSN.text;

Note, you need to assign the stored_procedure.Connection and call stored_procedure.Parameters.Refresh; before doing this

answered Dec 15 '14 at 05:49

sav

2,064
5
25
45

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

4 Answers4

Linked