Migrating to FireDAC from ADO – Stored Proc Param Type

Migrating to FireDAC from ADO – Stored Proc Param Type

Found an interesting little deviation today.  

In a MSSQL stored proc with an TinyInt parameter, the insert statement suddenly complained that it could not insert the param with NULL value (as per constraint).

After a lot of tracing, I was certain that we passed a 0 and not a NULL – and the only plausible reason was that I could see, was that the code was setting the param value, before setting the param datatype – so I rearranged the assignments to ensure that value was the last thing to be set.

That fixed the issue.  Go figure.

Params is Array of Variant.

// ADO, MSSQL

for i := Low(Params) to High(Params) do

begin

  p := AStoredProc.Parameters.AddParameter;

  p.Direction := pdInput;

  p.Value := Params[i];

  p.DataType := VariantTypeToFieldType(VarType(Params[i]));

  if p.Value = null

   then p.Attributes := [paNullable]

  else if VarType(Params[i]) = varBoolean

  then begin

    p.DataType := ftInteger;

    if Params[i]

     then p.Value:=1

      else p.Value:=0;

  end;

end;

// FireDAC, MSSQL

for i := Low(Params) to High(Params) do

begin

  p := AStoredProc.Params.Add;

  p.ParamType := TParamType.ptInput;

  vt := VarType(Params[i]);

  if vt vtBoolean

  then begin

    p.DataType := VariantTypeToFieldType(vt);

    p.Value := Params[i];

  end

  else begin

    p.DataType := ftInteger;

    if Params[i]

     then p.Value:=1

      else p.Value:=0;

  end;

end;