TADOStoredProc和SQL Server存储过程，参数为默认值 [英] TADOStoredProc and SQL Server stored procedure with parameter as default value

问题描述

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

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

我从Delphi调用上述过程，像这样：

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;

在Delphi代码中，我没有传递第二个参数，并且我希望SQL Server应该使用它默认值。

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?

如果我将参数值设置为 dummy，则它按预期工作。

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

推荐答案

TL; DR -要使用默认的SP参数值集：

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

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

---

---

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

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

是的。您在设计时使用的参数已经存在，因此该参数作为NULL显式发送到SQL Server。您可以通过检查SQL事件探查器来验证这一点。

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.

如果必须使用设计时参数，则只要在运行时删除 default 参数是要使用的，否则可以创建/分配它。例如（您可以为此创建一个通用方法）：

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;

否则，请勿使用设计时参数，并在需要时在运行时创建参数。 IMO的一种更好的方法是使用本地 TADOStoredProc ，在运行时创建它，分配参数，执行它，然后销毁它。

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 并致电 Parameters.Refresh（） 方法，它将查询（并创建）SQL Server中的参数。然后我只需将值分配给所需的参数即可。即使有额外的SQL Server行程，在向SP添加新参数时也非常方便维护。

如果未设置参数值，ADO将启动 exec 命令使用 default 关键字设置参数。即

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

---

进一步挖掘之后，我发现问题实际上出在 TParameter.SetValue 设置器。没有办法清除参数值：

---

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;

可以看到，如果设置了 Value 到 NULL 或 Unassigned ， ParameterObject.Value 设置为 NULL （但不要设置为默认值）。

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

因此，如果需要清除参数，并且使用默认值，您需要直接将 TParameter.ParameterObject.Value 设置为 Unassigned ：

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!'

这篇关于TADOStoredProc和SQL Server存储过程，参数为默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！