子查询返回多个值.当子查询遵循SQL Server过程时，不允许这样做

Question

为什么这个程序不工作?

CREATE PROCEDURE [dbo].[p_top5_v2_type_1km]
@IdCustomer int,
@idGroupVehicle int  
AS
DECLARE @start geography
SET @start = (SELECT location FROM Customer 
WHERE idCustomer = @idCustomer )
SELECT TOP 5 
idVehicle, idGroupVehicle, brand, model, maxRange, weight, maxSpeed, nameLocation 
FROM 
Vehicle 
WHERE 
idGroupVehicle = @idGroupVehicle 
AND @start.STDistance(locationVehicle) / 1000 <= 1
ORDER BY 
@start.STDistance(locationVehicle) / 1000 ASC
GO

这个程序应该列出最接近的前5辆车，类型是idGroupVehicle，但是我得到了这个错误

Msg 512, Level 16, State 1, Procedure p_top5_type, Line 6 [Batch Start Line 18]
子查询返回多个值。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，不允许这样做。

有人能解释一下为什么这不起作用吗?

Answer 1

subery =>(从客户中选择位置WHERE idCustomer = @idCustomer)返回多个值当您使用SET语法

时发生

我认为在表Customer中有double idCustomer

如果你可以在表Customer中使用double idCustomer

SET @start = (SELECT TOP 1 location FROM Customer 
WHERE idCustomer = @idCustomer )

或

SELECT @start = location FROM Customer 
WHERE idCustomer = @idCustomer