TADOStoredProc and SQL Server stored procedure with parameter as default value

Question

I am working with Delphi 7 and SQL Server 2008. I have created a dummy table and dummy stored procedure as shown below.

CREATE TABLE [dbo].[Persons]
(
    [P_ID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](255) NOT NULL
)

CREATE PROCEDURE [dbo].[p_dummy_proc]
    @p_id int,
    @p_name VARCHAR(10) = 'dummy' -- donot pass anything from delphi and it should take 'dummy'
AS
BEGIN 
    IF (@p_name is null)
        RAISERROR 123456 'why are you null'
    ELSE  
        INSERT INTO dbo.persons(LastName) 
        VALUES(@p_name)
END

I am calling above procedure from Delphi like this:

procedure TForm1.Button1Click(Sender: TObject);
begin
  try
    ADOStoredProc1.Parameters.ParamByName('@p_id').Value := 10;
    ADOStoredProc1.ExecProc; // error why are you null
  except
   on E: EDatabaseError do
     ShowMessage(e.Message);
  end;
end;

In the Delphi code, I am not passing second parameter and I am expecting that SQL Server should take it's default value.

When I execute the stored procedure, I am getting an errror. Why does SQL Server not take the default value when I didnt pass anything from Delphi?

if i set the parameter value to 'dummy' it is working as expected.

Answer 1

TL;DR - To use the default SP parameter value set:

ADOStoredProc1.Parameters.ParamByName('@p_name').ParameterObject.Value := Unassigned

---

In the Delphi code, I am not passing second parameter and I am expecting that SQL Server should take it's default value.

Yes you are. You are using an already existing parameter in design time, so this parameter is explicitly sent as NULL to the SQL Server. You can verify this by inspecting the SQL profiler.

If you must use design time parameters, you can delete it in run time whenever a default parameter is meant to be used or create/assign it otherwise. e.g (you could make a general method for this):

var
  Param: TParameter;
  ParamName: WideString;
  ParamValue: Variant;
  UseDefaultParameter: Boolean;
begin
  ParamName := '@p_name';
  Param := ADOStoredProc1.Parameters.FindParam(ParamName);
  UseDefaultParameter := True; // or False to assign a value

  if UseDefaultParameter then
  begin
    if Assigned(Param) then
      ADOStoredProc1.Parameters.Delete(Param.Index);
  end
  else
  begin
    ParamValue := 'boo!';
    if Assigned(Param) then
      Param.Value := ParamValue
    else
      ADOStoredProc1.Parameters.CreateParameter(ParamName, ftString, pdInput, 10, ParamValue);
  end;
end;

Otherwise, don't use design-time parameters and create the parameters at run-time as needed. An even better approach IMO is to use a local TADOStoredProc, create it at run-time, assign the parameters, execute it, and destroy it.

Personally, I create my TADOStoredProc at run-time and call Parameters.Refresh() method, which will query (and create) the parameters from the SQL Server. then I simply assign the values to the parameters I need. even though there is an extra trip to the SQL Server, it is very convenient to maintain when adding new parameters to the SP.
If a parameter value is not set, the ADO will initiate an exec command setting the parameter with the default keyword. i.e

exec p_dummy_proc @p_id=1, @p_name=default

---

After further digging, I noticed that the problem is actually in the TParameter.SetValue setter. there is no way to "clear" the parameter value:

procedure TParameter.SetValue(const Value: Variant);
begin
  if VarIsEmpty(Value) or VarIsNull(Value) then <--
    NewValue := Null
  else
  begin
    ...
  end;  
  ParameterObject.Value := NewValue;
end;

You can see that in case you set the Value to NULL or Unassigned, ParameterObject.Value will be set to NULL (but never to the default).

So if you need to clear a parameter, and use the default value, you need to directly set the TParameter.ParameterObject.Value to Unassigned:

if UseDefaultParameter then 
  SP.Parameters.ParamByName('@p_name').ParameterObject.Value := Unassigned
else
  SP.Parameters.ParamByName('@p_name').Value := 'boo!'