[Resolved]TSQLStoredProc : Last param no read.

[Hoppus]

Hi !

I have a problem with TSQLStoredProc.
I create any Stored Proc in SQL Server 2008 :

Create Procedure [dbo].[OPlan_InsertStatistiques]
@Date varchar(10),@Employe int,@Horaire int,
@Cloture int, @IdCloture int, @Theo int, @Eff int,
@Presence int, @Travaille int, @Justif int, @IdStat int OUTPUT
AS
BEGIN

INSERT INTO [pass35].[dbo].[Statistiques]
([Date]
,[Employe]
,[IndexHoraire]
,[Cloture]
,[IDCloture]
,[Theorique]
,[Effective]
,[Presence]
,[Travaille]
,[Justifie])
VALUES
(@Date,
@Employe,
@Horaire,
@Cloture,
@IDCloture,
@Theo,
@Eff,
@Presence,
@Travaille,
@Justif)

SELECT @IdStat = SCOPE_IDENTITY()
END

I but in my Delphi 6 project any TSQLStoredProc and here's the code :

with DMStatistiques.sqlInsertStats do begin //My TSQLStoredProc
StoredProcName := 'OPlan_InsertStatistiques';
Params.Clear;
Params.CreateParam(ftString,'@Date',ptInput) ;
Params.CreateParam(ftInteger,'@Employe',ptInput) ;
Params.CreateParam(ftInteger,'@Horaire',ptInput);
Params.CreateParam(ftInteger,'@Cloture',ptInput);
Params.CreateParam(ftInteger,'@IdCloture',ptInput);
Params.CreateParam(ftInteger,'@Theo',ptInput);
Params.CreateParam(ftInteger,'@Eff',ptInput);
Params.CreateParam(ftInteger,'@Presence',ptInput);
Params.CreateParam(ftInteger,'@Travaille',ptInput);
Params.CreateParam(ftInteger,'@Justif',ptInput);
Params.CreateParam(ftInteger,'@IdStat',ptInputOutput);
end;

DMStatistiques.sqlInsertStats.ParamByName('@Date').AsString := FormateDateSGBD(date,false,true,true);
DMStatistiques.sqlInsertStats.ParamByName('@Employe').AsInteger := id_employe;
DMStatistiques.sqlInsertStats.ParamByName('@Horaire').AsInteger := id_horaire;
DMStatistiques.sqlInsertStats.ParamByName('@Cloture').AsInteger := 0;
DMStatistiques.sqlInsertStats.ParamByName('@IdCloture').Clear;
DMStatistiques.sqlInsertStats.ParamByName('@Theo').AsInteger := theorique_entier;
DMStatistiques.sqlInsertStats.ParamByName('@Eff').AsInteger := effectif_entier;
DMStatistiques.sqlInsertStats.ParamByName('@Presence').AsInteger := presence_entier;
DMStatistiques.sqlInsertStats.ParamByName('@Travaille').AsInteger := ecrete_entier;
DMStatistiques.sqlInsertStats.ParamByName('@Justif').AsInteger := justifie_entier;
DMStatistiques.sqlInsertStats.ParamByName('@IdStat').AsInteger := 0;
DMStatistiques.sqlInsertStats.ExecProc;
// Insertion
Result := DMStatistiques.sqlInsertStats.ParamByName('@IdStat').AsInteger;

I always have any error telling me (traduced from french):

SQL Error Code 201 : The procedure is wainting for the param "@IdStat" who's not given"

If i put the param @IdStat in the beginning of my declaration, he tell me that @Justif is wainting...
if i put all in ptInput and i remove the OUTPUT value in my proc, it's the same.

Can you help me please ?

Regards

Hop'

[AndreyZ]

Hello,

The point is that there is the result parameter that you don't create. To avoid the problem, you should use the following code:

Code: Select all

with DMStatistiques.sqlInsertStats do begin //My TSQLStoredProc
  StoredProcName := 'OPlan_InsertStatistiques'; 
  Params.Clear; 
  Params.CreateParam(ftInteger, '@RETURN_VALUE', ptResult);
  Params.CreateParam(ftString,'@Date',ptInput);
  ...

Also, you can let dbExpress driver for SQL Server create parameters automatically. Please note that in this case you must not use the '@' symbol in parameters names. Here is an example:

Code: Select all

with DMStatistiques.sqlInsertStats do begin
  ParamCheck := True;
  StoredProcName := 'OPlan_InsertStatistiques';
  ParamByName('Date').AsString := FormateDateSGBD(date,false,true,true);
  ParamByName('Employe').AsInteger := id_employe;
  ParamByName('Horaire').AsInteger := id_horaire;
  ParamByName('Cloture').AsInteger := 0;
  ParamByName('IdCloture').Clear;
  ParamByName('Theo').AsInteger := theorique_entier;
  ParamByName('Eff').AsInteger := effectif_entier;
  ParamByName('Presence').AsInteger := presence_entier;
  ParamByName('Travaille').AsInteger := ecrete_entier;
  ParamByName('Justif').AsInteger := justifie_entier;
  ParamByName('IdStat').AsInteger := 0;
  ExecProc;
end;

[Hoppus]

It works fine with Return Value

Thank you

[AndreyZ]

If any other questions come up, please contact us.