TADOStoredProc和SQL Server存储过程以参数作为默认值

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

我正在使用Delphi 7和SQL Server 2008。我创建了一个虚拟表和虚拟存储过程，如下所示。

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.

在Delphi代码中，我没有传递第二个参数，我希望SQL Server接受它的默认值。

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?

当我执行存储过程时，会得到一个错误。当我没有从Delphi中传递任何内容时，为什么SQL Server不接受默认值?

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

如果我将参数值设置为“哑”，那么它将按照预期工作。

1 个解决方案

#1

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

TL:使用默认的SP参数值集:

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.

在Delphi代码中，我没有传递第二个参数，我希望SQL Server接受它的默认值。

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.

是的,你是。在设计时，您正在使用一个已经存在的参数，因此这个参数被显式地作为NULL发送给SQL服务器。您可以通过检查SQL分析器来验证这一点。

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

如果必须使用设计时参数，可以在运行时删除默认参数，或者创建或分配默认参数。e。g(您可以为此制定一个通用的方法):

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.

否则，不要使用设计时参数并在运行时根据需要创建参数。一个更好的方法是使用本地TADOStoredProc，在运行时创建它，分配参数，执行它，并销毁它。

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

就我个人而言，我在运行时创建了TADOStoredProc并调用parameters . refresh()方法，该方法将从SQL服务器查询(并创建)参数。然后，我只需将值赋给所需的参数。即使要额外访问SQL服务器，在向SP添加新参数时也很容易维护。如果没有设置参数值，ADO将启动一个exec命令，使用默认关键字设置参数。即

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:

在进一步挖掘之后，我注意到问题实际上在TParameter中。SetValue setter。没有办法“清除”参数值:

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

您可以看到，如果您将值设置为NULL或未赋值的ParameterObject。值将被设置为NULL(但不会设置为默认值)。

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

因此，如果需要清除一个参数并使用默认值，则需要直接设置TParameter.ParameterObject。未赋值的值:

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

#1