如何编写一个不传递参数的过程?

Question

我有一个有四个参数的过程，其中一个参数是必需的，但其他3个参数不是必需的。

过程看起来像这样:

CREATE PROCEDURE p_choose_vehicle_byGroup_resultCount_Radiant5
@IdCustomer int,
@idGroupVehicle int = null,
@ResultCount int= null,
@Radiant int= null 
AS
DECLARE @start geography
SET @start = (SELECT location FROM Customer WHERE idCustomer = @idCustomer)
IF @ResultCount IS NULL AND @idGroupVehicle IS NULL AND @Radiant IS NULL
SELECT 
idVehicle, idGroupVehicle, brand, model, maxRange, weight, 
maxSpeed, nameLocation, @start.STDistance(locationVehicle) / 1000 AS distanceInKm
FROM
Vehicle 
WHERE 
(@start.STDistance(locationVehicle) / 1000 IS NOT NULL)
ORDER BY 
@start.STDistance(locationVehicle) / 1000 ASC
else if @ResultCount is null and @Radiant is null
select  idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
from Vehicle 
where  idGroupVehicle= @idGroupVehicle and (@start.STDistance(locationVehicle)/1000  is not null)
order by @start.STDistance(locationVehicle)/1000 asc
else if @Radiant is null
select TOP(@ResultCount) idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
from Vehicle 
where idGroupVehicle= @idGroupVehicle  and  (@start.STDistance(locationVehicle)/1000  is not null)
order by @start.STDistance(locationVehicle)/1000 asc
else
select TOP(@ResultCount) idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
from Vehicle 
where idGroupVehicle= @idGroupVehicle  and  (@start.STDistance(locationVehicle)/1000  <= @Radiant)
order by @start.STDistance(locationVehicle)/1000 asc
GO

这个过程的工作，但我的问题是有任何方法来改进这个过程，因为当我想要执行p_choose_vehicle_byGroup_resultCount_Radiant5 1是好的1,1，是好的，但当我尝试选择idCustomer和resultCount我必须这样做1,null,1，但我想不写null，选择这个选项是有任何方法来改进这个过程吗?

Answer 1

除非我不理解，否则您只需要像

这样调用您的过程

exec p_choose_vehicle_byGroup_resultCount_Radiant5 @IdCustomer=1, @resultCount=1