如何避免在 WHERE 子句中使用变量

Parameter sniffing is your friend almost all of the time and you should write your queries so that it can be used. Parameter sniffing helps building the plan for you using the parameter values available when the query is compiled. The dark side of parameter sniffing is when the values used when compiling the query is not optimal for the queries to come.

The query in a stored procedure is compiled when the stored procedure is executed, not when the query is executed so the values that SQL Server has to deal with here...

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

is a known value for @endDate and an unknown value for @startDate. That will leave SQL Server to guessing on 30% of the rows returned for the filter on @startDate combined with whatever the statistics tells it for @endDate. If you have a big table with a lot of rows that could give you a scan operation where you would benefit most from a seek.

Your wrapper procedure solution makes sure that SQL Server sees the values when DateRangeProc is compiled so it can use known values for both @endDate and @startDate.

Both your dynamic queries leads to the same thing, the values are known at compile-time.

The one with a default null value is a bit special. The values known to SQL Server at compile-time is a known value for @endDate and null for @startDate. Using a null in a between will give you 0 rows but SQL Server always guess at 1 in those cases. That might be a good thing in this case but if you call the stored procedure with a large date interval where a scan would have been the best choice it may end up doing a bunch of seeks.

I left "Use the DATEADD() function directly" to the end of this answer because it is the one I would use and there is something strange with it as well.

First off, SQL Server does not call the function multiple times when it is used in the where clause. DATEADD is considered runtime constant.

And I would think that DATEADD is evaluated when the query is compiled so that you would get a good estimate on the number of rows returned. But it is not so in this case.
SQL Server estimates based on the value in the parameter regardless of what you do with DATEADD (tested on SQL Server 2012) so in your case the estimate will be the number of rows that is registered on @endDate. Why it does that I don't know but it has to do with the use of the datatype DATE. Shift to DATETIME in the stored procedure and the table and the estimate will be accurate, meaning that DATEADD is considered at compile time for DATETIME not for DATE.

So to summarize this rather lengthy answer I would recommend the wrapper procedure solution. It will always allow SQL Server to use the values provided when compiling the the query without the hassle of using dynamic SQL.

PS:

In comments you got two suggestions.

OPTION (OPTIMIZE FOR UNKNOWN) will give you an estimate of 9% of rows returned and OPTION (RECOMPILE) will make SQL Server see the parameter values since the query is recompiled every time.

好的，我有两个可能的解决方案给你。

首先，我想知道这是否允许增加参数化。我还没有机会测试它，但它可能会起作用。

CREATE PROCEDURE WeeklyProc(@endDate DATE, @startDate DATE)
AS
BEGIN
  IF @startDate IS NULL
    SET @startDate = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

另一个选项利用了您使用固定时间框架的事实。首先创建一个 DateLookup 表。像这样的东西

CurrentDate    8WeekStartDate    8WeekEndDate    etc

填写从现在到下个世纪之间的每个日期。这只有约 36500 行，所以是一个相当小的表。然后像这样更改您的查询

IF @Range = '8WeekRange' 
    SELECT
      -- Stuff
    FROM Sale
    JOIN DateLookup
        ON SaleDate BETWEEN [8WeekStartDate] AND [8WeekEndDate]
    WHERE DateLookup.CurrentDate = GetDate()

显然这只是一个例子，当然可以写得更好，但我对这种类型的表很幸运。特别是因为它是一个静态表，可以像疯了一样被索引。

(It's 2020 now, and I am surprised and disappointed that the SQL language still doesn't have a built-in predicate builder syntax to allow WHERE clauses to be constructed in a safe and verifiable manner without resorting to Dynamic SQL. Though in many applications this is moot because the ORM will handle query generation, and I am in love with Entity Framework and Linq-to-Entities - but this isn't available for people needing to write queries by hand).

In my case, I had a Multi-Statement Table-Valued Function that had a single SELECT query with WHERE clauses that I needed to disable or enable at runtime, and using the @param IS NULL OR [Col] = @param "trick" didn't work because it was generating suboptimal execution plans. The query was rather complicated with a load of JOINs as well, but the WHERE clauses I wanted to customize was in the outer-query, something like this:

CREATE FUNCTION OriginalComplicatedQuery( 
    @optionalMinDate date,
    @optionalMaxDate date,
    @optionalName    nvarchar(50)
)
RETURNS @results TABLE (
    personId int          NOT NULL PRIMARY KEY,
    created  datetime2(7) NOT NULL,
    name     nvarchar(50) NOT NULL,
    etc      etc          NOT NULL
)
AS
BEGIN
    
    INSERT INTO @results ( personId, created, name, etc )
    SELECT
        personId,
        created,
        name,
        etc
    FROM
        -- Pretend this FROM clause is much more complicated than this!
        dbo.People AS p
        INNER JOIN dbo.OtherTable AS o ON p.FooId = o.BarId
    WHERE
        (
            @optionalMinDate IS NULL
            OR
            created >= @optionalMinDate
        )
        AND
        (
            @optionalMaxDate IS NULL
            OR
            created >= @optionalMaxDate
        )
        AND
        (
            @optionalName IS NULL
            OR
            name LIKE @optionalName
        );
    
    RETURN;

END;

What I did was move the complicated FROM into a VIEW or Inline Table Valued Function, and then created a tree of IF statements for each combination of predicates. It's big - but it isn't complicated and it does mean the optimal query plan is always generated.

If you have a lot of queries like this, you could use T4 to generate the IF branches for each set of parameters (I find myself having to use T4 to generate repetitive SQL anyway, because T-SQL doesn't have built-in support for macros).

So I currently have this instead:

CREATE FUNCTION BetterComplicatedQuery( 
    @optionalMinDate date,
    @optionalMaxDate date,
    @optionalName    nvarchar(50)
)
RETURNS @results TABLE (
    personId int          NOT NULL PRIMARY KEY,
    created  datetime2(7) NOT NULL,
    name     nvarchar(50) NOT NULL,
    etc      etc          NOT NULL
)
AS
BEGIN
    
    IF @optionalMinDate IS NOT NULL
    BEGIN;
        
        IF @optionalMaxDate IS NOT NULL
        BEGIN;
            
            IF @optionalName IS NOT NULL;
            BEGIN;
                
                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin()
                WHERE
                    created >= @optionalMinDate
                    AND
                    created < @optionalMaxDate
                    AND
                    name LIKE @optionalName;
                
                
            END;
            ELSE; --  @optionalName IS NULL;

                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin()
                WHERE
                    created >= @optionalMinDate
                    AND
                    created < @optionalMaxDate;

            END;
            
        END;
        ELSE; -- @optionalMaxDate IS NULL
        BEGIN;

            IF @optionalName IS NOT NULL;
            BEGIN;
                
                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin()
                WHERE
                    created >= @optionalMinDate
                    AND
                    name LIKE @optionalName;
                
                
            END;
            ELSE; --  @optionalName IS NULL;

                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin()
                WHERE
                    created >= @optionalMinDate;

            END;
        END;
        
    END;
    ELSE; -- @optionalMinDate IS NULL
    BEGIN;
        
        IF @optionalMaxDate IS NOT NULL
        BEGIN;
            
            IF @optionalName IS NOT NULL;
            BEGIN;
                
                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin()
                WHERE
                    created < @optionalMaxDate
                    AND
                    name LIKE @optionalName;
                
                
            END;
            ELSE; --  @optionalName IS NULL;

                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin()
                WHERE
                    created < @optionalMaxDate;

            END;
            
        END;
        ELSE; -- @optionalMaxDate IS NULL
        BEGIN;

            IF @optionalName IS NOT NULL;
            BEGIN;
                
                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin()
                WHERE
                    name LIKE @optionalName;
                
                
            END;
            ELSE; --  @optionalName IS NULL;

                INSERT INTO @results ( personId, created, name, etc )
                SELECT
                    personId, created, name, etc
                FROM
                    dbo.InlineTableValueFunctionForTheJoin();

            END;
        END;
        
    END;
    
    RETURN;

END;

Fantasy time:

I just wish the SQL language design team would add built-in macros and/or a predicate-builder. There's no reason why something like this couldn't exist:

CREATE FUNCTION EvenBetterComplicatedQuery( 
    @optionalMinDate date,
    @optionalMaxDate date,
    @optionalName    nvarchar(50)
)
RETURNS @results TABLE (
    personId int          NOT NULL PRIMARY KEY,
    created  datetime2(7) NOT NULL,
    name     nvarchar(50) NOT NULL,
    etc      etc          NOT NULL
)
AS
    
    -- Wish 1: Allow a CTE to be constructed independently of a SELECT query.
    WITH cte AS
        SELECT
            personId, created, name, etc
        FROM
            dbo.InlineTableValueFunctionForTheJoin()
    ;
        
    -- Wish 2: A strongly-typed PREDICATE type (that operates on a CTE) to build a WHERE clause:
    DECLARE @predicate PREDICATE( cte ) = TRUE;
    
    -- Append clauses to the @predicate using boolean expressions:
    IF @optionalMinDate IS NOT NULL
    BEGIN;
        @predicate = AND( @predicate, created => @optionalMinDate );
    END;

    IF @optionalMaxDate IS NOT NULL
    BEGIN;
        @predicate = AND( @predicate, created < @optionalMaxDate );
    END;

    IF @optionalName IS NOT NULL
    BEGIN;
        @predicate = AND( @predicate, name LIKE @optionalName );
    END;

    -- And then add it to a SELECT statement:
    INSERT INTO @results ( personId, created, name, etc )
    SELECT
        personId, created, name, etc
    FROM
        cte
    WHERE
        @predicate( cte );
    
    RETURN;

END;

And if we had the ability to define a list of columns as a macro that'd make it even sweeter:

DECLARE $personData AS COLUMN_LIST_MACRO (
    personId int          NOT NULL PRIMARY KEY,
    created  datetime2(7) NOT NULL,
    name     nvarchar(50) NOT NULL,
    etc      etc          NOT NULL
);

CREATE FUNCTION DearIsoSqlPeoplePleaseMakeThisPossible( 
    @optionalMinDate date,
    @optionalMaxDate date,
    @optionalName    nvarchar(50)
)
RETURNS @results TABLE (
    $personData
)
AS
    
    -- Wish 1: Allow a CTE to be constructed independently of a SELECT query.
    WITH cte AS
        SELECT
            $personData
        FROM
            dbo.InlineTableValueFunctionForTheJoin()
    ;
        
    -- Wish 2: A strongly-typed PREDICATE type (that operates on a CTE) to build a WHERE clause:
    DECLARE @predicate PREDICATE( cte ) = TRUE;
    
    -- Append clauses to the @predicate using boolean expressions:
    IF @optionalMinDate IS NOT NULL
    BEGIN;
        @predicate = AND( @predicate, created => @optionalMinDate );
    END;

    IF @optionalMaxDate IS NOT NULL
    BEGIN;
        @predicate = AND( @predicate, created < @optionalMaxDate );
    END;

    IF @optionalName IS NOT NULL
    BEGIN;
        @predicate = AND( @predicate, name LIKE @optionalName );
    END;

    -- And then add it to a SELECT statement:
    INSERT INTO @results ( $personData )
    SELECT
        $personData
    FROM
        cte
    WHERE
        @predicate( cte );
    
    RETURN;

END;

...but I can dream :/