存储过程中的可选参数问题

I have this stored procedure

我有这个存储过程

ALTER PROCEDURE [dbo].[usp_mySP]
    -- Add the parameters for the stored procedure here
    -- For inventory table check.
    @DealerID       VARCHAR(50),
    @Certified      VARCHAR(50) = NULL,
    -- For fuel value.
    @CityFeatureValue   VARCHAR(50),
    @HwyFeatureValue    VARCHAR(50)

AS
BEGIN
        BEGIN
        SELECT  InventoryID, 
                VIN
        FROM    Inventory
        WHERE   DealerID = @DealerID
                AND Deleted     =   'False'
                AND (IsPending  =   '0'         OR IsPending    IS NULL) 
                --AND (Certified    =   @Certified  OR @Certified   IS NULL)

                AND VIN IN
                        (
                            SELECT      VIN 
                            FROM        FuelPerformance
                            WHERE       (
                                            FeatureTitle = 'Fuel Economy (City)' 
                                            AND FeatureValue = @CityFeatureValue
                                        )
                                        OR 
                                        (
                                            FeatureTitle = 'Fuel Economy (Hwy)' 
                                            AND FeatureValue = @HwyFeatureValue
                                        )
                            GROUP BY    VIN
                            HAVING      COUNT(VIN) > 1
                        )
    END
END

I am calling it like :

我称之为：

EXEC usp_ListOfVehiclesOnFuelCondition_ForSingleDealer
   '09f5245d' , '', '18', '28'

When I am commenting the line

当我评论这条线时

 AND (Certified = @Certified OR @Certified IS NULL)

it is giving the result, but when this line is there the result is blank.

它给出了结果，但是当这一行出现时，结果是空白的。

Any suggestion where I am doing wrong?

我做错了什么建议？

1 个解决方案

#1

You are passing in an empty string (''). That is very different from NULL.

你传入一个空字符串（''）。这与NULL非常不同。

Pass in the parameters as named parameters (without @Certified), or test for an empty string, or pass in a NULL.

将参数作为命名参数传递（不带@Certified），或测试空字符串，或传入NULL。

Named parameters:

命名参数：

EXEC usp_ListOfVehiclesOnFuelCondition_ForSingleDealer @DealerId = '09f5245d',
                                                       @CityFeatureValue = '18', 
                                                       @HwyFeatureValue = '28'

Passing a NULL:

传递NULL：

EXEC usp_ListOfVehiclesOnFuelCondition_ForSingleDealer '09f5245d', 
                                                       NULL, 
                                                       '18', 
                                                       '28'

Checking for '':

检查''：

AND (Certified = @Certified OR @Certified IS NULL OR @Certified = '')

#1