如何将TADOQuery.Parameters与整数参数类型一起使用，这些参数类型必须放在查询的两个或多个位置？

I have a complex query that contains more than one place where the same primary key value must be substituted. It looks like this:

我有一个复杂的查询，其中包含多个必须替换相同主键值的位置。它看起来像这样：

select  Foo.Id,
        Foo.BearBaitId,
        Foo.LinkType,
        Foo.BugId,
        Foo.GooNum,
        Foo.WorkOrderId,
        (case when Goo.ZenID is null or Goo.ZenID=0 then
            IsNull(dbo.EmptyToNull(Bar.FanName),dbo.EmptyToNull(Bar.BazName))+' '+Bar.Strength else
            '@'+BarZen.Description end) as Description,
        Foo.Init,
        Foo.DateCreated,
        Foo.DateChanged,
        Bug.LastName,
        Bug.FirstName,
        Goo.BarID,
        (case when Goo.ZenID is null or Goo.ZenID=0 then
             IsNull(dbo.EmptyToNull(Bar.BazName),dbo.EmptyToNull(Bar.FanName))+' '+Bar.Strength else
             '@'+BarZen.Description end) as BazName,
        GooTracking.Status as GooTrackingStatus
  from
    Foo
  inner join Bug on (Foo.BugId=Bug.Id)
  inner join Goo on (Foo.GooNum=Goo.GooNum)
  left join Bar on (Bar.Id=Goo.BarID)
  left join BarZen on (Goo.ZenID=BarZen.ID)
  inner join  GooTracking on(Goo.GooNum=GooTracking.GooNum )
 where (BearBaitId = :aBaitid) 
UNION
 select Foo.Id,
        Foo.BearBaitId,
        Foo.LinkType,
        Foo.BugId,
        Foo.GooNum,
        Foo.WorkOrderId,
        Foo.Description,
        Foo.Init,
        Foo.DateCreated,
        Foo.DateChanged,
        Bug.LastName,
        Bug.FirstName,
        0,
        NULL,
        0
 from Foo
 inner join Bug on (Foo.BugId=Bug.Id)
  where (LinkType=0)  and (BearBaitId= :aBaitid ) 
order by BearBaitId,LinkType desc, GooNum

When I try to use an integer parameter on this non-trivial query, it seems impossible to me. I get this error:

当我尝试在这个非平凡的查询中使用整数参数时，对我来说似乎是不可能的。我收到此错误：

Error

Incorrect syntax near ':'.

The query works fine if I take out the :aBaitid and substitute a literal 1.

如果我取出：aBaitid并替换文字1，查询工作正常。

Is there something else I can do to this query above? When I test with simple tests like this:

我可以对上面的查询做些什么吗？当我用这样的简单测试进行测试时：

select * from foo where id = :anid

These simple cases work fine. The component is TADOQuery, and it works fine until you add any :parameters to the SQL string.

这些简单的案例很好。该组件是TADOQuery，它可以正常工作，直到您将任何：参数添加到SQL字符串。

Update: when I use the following code at runtime, the parameter substitutions are actually done (some glitch in the ADO components is worked around) and a different error surfaces:

更新：当我在运行时使用以下代码时，实际完成了参数替换（ADO组件中的一些小故障）以及不同的错误表面：

adoFooContentQuery.Parameters.FindParam('aBaitId').Value := 1;
adoFooContentQuery.Active := true;

Now the error changes to:

现在错误变为：

Incorrect syntax near the keyword 'inner''.

Note again, that this error goes away if I simply stop using the parameter substitution feature.

请再次注意，如果我只是停止使用参数替换功能，则此错误会消失。

Update2: The accepted answer suggests I have to find two different copies of the parameter with the same name, which bothered me so I reworked the query like this:

Update2：接受的答案表明我必须找到两个具有相同名称的参数的不同副本，这让我感到困扰，所以我重新编写了这样的查询：

 DECLARE @aVar int;
 SET @aVar = :aBaitid;
 SELECT ....(long query here)

Then I used @aVar throughout the script where needed, to avoid the repeated use of :aBaitId. (If the number of times the parameter value is used changes, I don't want to have to find all parameters matching a name, and replace them).

然后我在需要的整个脚本中使用了@aVar，以避免重复使用：aBaitId。 （如果使用参数值的次数发生变化，我不希望必须找到与名称匹配的所有参数，并替换它们）。

I suppose a helper-function like this would be fine too: SetAllParamsNamed(aQuery:TAdoQuery; aName:String;aValue:Variant)

我想像这样的辅助函数也可以：SetAllParamsNamed（aQuery：TAdoQuery; aName：String; aValue：Variant）

1 个解决方案

#1

FindParam only finds one parameter, while you have two with the same name. Delphi dataset adds each parameter as a separate one to its collection of parameters.

FindParam只找到一个参数，而你有两个同名的参数。 Delphi数据集将每个参数作为单独的参数添加到其参数集合中。

It should work if you loop through all parameters, check if the name matches, and set the value of each one that matches, although I normally choose to give each same parameter a follow-up number to distingish between them.

如果循环遍历所有参数，检查名称是否匹配，并设置匹配的每个参数的值，它应该有效，尽管我通常选择为每个相同的参数提供后续数字以便在它们之间进行修改。