Caching Stored procedure parameters

[oodesigns]

Hi

This will be quite long

Firstly the basics
>>>>>>>>>
I have a an application server using TMSStoredProc. I pass a message from the client to execute a proc. It loads the required proc and excutes it coping over any paramters required. All parameters passed from the client are strings the TMSStoredProc has to load the paramters form the database and assign them based on name.
>>>>>>>>

What I have done to cache the params may not be reliable and I would like you advise to see if it can be done better.

Firstly Caching the params reduces the round trips and makes it twice as fast.

1) Does this storedprocedure have cached params if not create a dummy stored procedure and get them.
2) Yes it does have params so copy them over.

Here is the code.

I use the same TMSStoredProc for many different proc calls.

sp := ProcList[newProcName]);
If Assigned(sp) then begin
// Set the paramters up
FMSStoredProc.Params.Clear; // Clear all the parameters
for ParamIndex := 0 to sp.Params.Count -1 do begin
CachedParam := TDAParam(sp.Params[ParamIndex]);
NewParam := TDAParam(FMSStoredProc.Params.Add);
NewParam.DataType := CachedParam.DataType;
NewParam.ParamType := CachedParam.ParamType;
NewParam.Name := CachedParam.Name;
NewParam.Size := CachedParam.Size;
NewParam.Precision := CachedParam.Precision;
NewParam.NumericScale := CachedParam.NumericScale;
end;
// Make sure the OLEDB call matches so that is does not need to be created
FMSStoredProc.SQL.Text := sp.SQL.Text;
// Set the internal name rather than StoredProcName as this will prepare
TCustomMSStoredProcHack(FMSStoredProc).FStoredProcName := newProcName;
end else begin
FMSStoredProc.StoredProcName := newProcName;
sp := TMSStoredProc.Create(Nil);
sp.Params.Assign(Params);
sp.SQL.Text := SQL.Text;
ProcList[newProcName] := Sp;
end;

[Jackson]

Your method is correct.
But we advice to create pool of TMSStoredProc components instead of TMSParam objects.
It will save you from TCustomMSStoredProcHack class and simplify your application.
Object of TMSStoredProc class doesn't take a lot of memory.
You can even create something like garbage collector to destroy components which weren't in use for some priod of time.
If it is not applicable for your task then please describe your situation more detailed.