How to avoid using variables in WHERE clause

Question

Given a (simplified) stored procedure such as this:

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

If the Sale table is large the SELECT can take a long time to execute, apparently because the optimizer can't optimize due to the local variable. We tested running the SELECT part with variables then hard coded dates and the execution time went from ~9 minutes to ~1 second.

We have numerous stored procedures that query based on "fixed" date ranges (week, month, 8-week etc) so the input parameter is just @endDate and @startDate is calculated inside the procedure.

The question is, what is the best practice for avoiding variables in a WHERE clause so as not to compromise the optimizer?

The possibilities we came up with are shown below. Are any of these best practice, or is there another way?

Use a wrapper procedure to turn the variables into parameters.

Parameters don't affect the optimizer the same way local variables do.

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
   DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
   EXECUTE DateRangeProc @startDate, @endDate
END

CREATE PROCEDURE DateRangeProc(@startDate DATE, @endDate DATE)
AS
BEGIN
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

Use parameterized dynamic SQL.

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  DECLARE @sql NVARCHAR(4000) = N'
    SELECT
      -- Stuff
    FROM Sale
    WHERE SaleDate BETWEEN @startDate AND @endDate
  '
  DECLARE @param NVARCHAR(4000) = N'@startDate DATE, @endDate DATE'
  EXECUTE sp_executesql @sql, @param, @startDate = @startDate, @endDate = @endDate
END

Use "hard-coded" dynamic SQL.

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  DECLARE @sql NVARCHAR(4000) = N'
    SELECT
      -- Stuff
    FROM Sale
    WHERE SaleDate BETWEEN @startDate AND @endDate
  '
  SET @sql = REPLACE(@sql, '@startDate', CONVERT(NCHAR(10), @startDate, 126))
  SET @sql = REPLACE(@sql, '@endDate', CONVERT(NCHAR(10), @endDate, 126))
  EXECUTE sp_executesql @sql
END

Use the DATEADD() function directly.

I'm not keen on this because calling functions in the WHERE also affects performance.

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

Use an optional parameter.

I'm not sure if assigning to parameters would have the same problem as assigning to variables, so this might not be an option. I don't really like this solution but including it for completeness.

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

-- Update --

Thanks for suggestions and comments. After reading them I ran some timing tests with the various approaches. I'm adding the results here as a reference.

Run 1 is without a plan. Run 2 is immediately after Run 1 with exactly the same parameters so it will use the plan from run 1.

The NoProc times are for running the SELECT queries manually in SSMS outside a stored procedure.

TestProc1-7 are the queries from the original question.

TestProcA-B are based on the suggestion by Mikael Eriksson. The column in the database is a DATE so I tried passing the parameter as a DATETIME and running with implicit casting (testProcA) and explicit casting (testProcB).

TestProcC-D are based on the suggestion by Kenneth Fisher. We already use a date lookup table for other things, but we don't have one with a specific column for each period range. The variation I tried still uses BETWEEN but does it on the smaller lookup table and joins to the larger table. I'm going to investigate further as to whether we can use specific lookup tables, although our periods are fixed there are quite a few different ones.

    Total rows in Sale table: 136,424,366

                       Run 1 (ms)     Run 2 (ms)
    Procedure          CPU   Elapsed  CPU    Elapsed  Comment
    NoProc constants   6567  62199    2870   719      Manual query with constants
    NoProc variables   9314  62424    3993   998      Manual query with variables
    testProc1          6801  62919    2871   736      Hard coded range
    testProc2          8955  63190    3915   979      Parameter and variable range
    testProc3          8985  63152    3932   987      Wrapper procedure with parameter range
    testProc4          9142  63939    3931   977      Parameterized dynamic SQL
    testProc5          7269  62933    2933   728      Hard coded dynamic SQL
    testProc6          9266  63421    3915   984      Use DATEADD on DATE
    testProc7          2044  13950    1092  1087      Dummy parameter
    testProcA         12120  61493    5491  1875      Use DATEADD on DATETIME without CAST
    testProcB          8612  61949    3932   978      Use DATEADD on DATETIME with CAST
    testProcC          8861  61651    3917   993      Use lookup table, Sale first
    testProcD          8625  61740    3994  1031      Use lookup table, Sale last

Here's the test code.

------ SETUP ------

IF OBJECT_ID(N'testDimDate', N'U') IS NOT NULL DROP TABLE testDimDate
IF OBJECT_ID(N'testProc1', N'P') IS NOT NULL DROP PROCEDURE testProc1
IF OBJECT_ID(N'testProc2', N'P') IS NOT NULL DROP PROCEDURE testProc2
IF OBJECT_ID(N'testProc3', N'P') IS NOT NULL DROP PROCEDURE testProc3
IF OBJECT_ID(N'testProc3a', N'P') IS NOT NULL DROP PROCEDURE testProc3a
IF OBJECT_ID(N'testProc4', N'P') IS NOT NULL DROP PROCEDURE testProc4
IF OBJECT_ID(N'testProc5', N'P') IS NOT NULL DROP PROCEDURE testProc5
IF OBJECT_ID(N'testProc6', N'P') IS NOT NULL DROP PROCEDURE testProc6
IF OBJECT_ID(N'testProc7', N'P') IS NOT NULL DROP PROCEDURE testProc7
IF OBJECT_ID(N'testProcA', N'P') IS NOT NULL DROP PROCEDURE testProcA
IF OBJECT_ID(N'testProcB', N'P') IS NOT NULL DROP PROCEDURE testProcB
IF OBJECT_ID(N'testProcC', N'P') IS NOT NULL DROP PROCEDURE testProcC
IF OBJECT_ID(N'testProcD', N'P') IS NOT NULL DROP PROCEDURE testProcD
GO

CREATE TABLE testDimDate
(
   DateKey DATE NOT NULL,
   CONSTRAINT PK_DimDate_DateKey UNIQUE NONCLUSTERED (DateKey ASC)
)
GO

DECLARE @dateTimeStart DATETIME = '2000-01-01'
DECLARE @dateTimeEnd DATETIME = '2100-01-01'
;WITH CTE AS
(
   --Anchor member defined
   SELECT @dateTimeStart FullDate
   UNION ALL
   --Recursive member defined referencing CTE
   SELECT FullDate + 1 FROM CTE WHERE FullDate + 1 <= @dateTimeEnd
)
SELECT
   CAST(FullDate AS DATE) AS DateKey
INTO #DimDate
FROM CTE
OPTION (MAXRECURSION 0)

INSERT INTO testDimDate (DateKey)
SELECT DateKey FROM #DimDate ORDER BY DateKey ASC

DROP TABLE #DimDate
GO

-- Hard coded date range.
CREATE PROCEDURE testProc1 AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN '2012-12-09' AND '2012-12-10'
END
GO

-- Parameter and variable date range.
CREATE PROCEDURE testProc2(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
END
GO

-- Parameter date range.
CREATE PROCEDURE testProc3a(@startDate DATE, @endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
END
GO

-- Wrapper procedure.
CREATE PROCEDURE testProc3(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   EXEC testProc3a @startDate, @endDate
END
GO

-- Parameterized dynamic SQL.
CREATE PROCEDURE testProc4(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   DECLARE @sql NVARCHAR(4000) = N'SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate'
   DECLARE @param NVARCHAR(4000) = N'@startDate DATE, @endDate DATE'
   EXEC sp_executesql @sql, @param, @startDate = @startDate, @endDate = @endDate
END
GO

-- Hard coded dynamic SQL.
CREATE PROCEDURE testProc5(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   DECLARE @sql NVARCHAR(4000) = N'SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN ''@startDate'' AND ''@endDate'''
   SET @sql = REPLACE(@sql, '@startDate', CONVERT(NCHAR(10), @startDate, 126))
   SET @sql = REPLACE(@sql, '@endDate', CONVERT(NCHAR(10), @endDate, 126))
   EXEC sp_executesql @sql
END
GO

-- Explicitly use DATEADD on a DATE.
CREATE PROCEDURE testProc6(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN DATEADD(DAY, -1, @endDate) AND @endDate
END
GO

-- Dummy parameter.
CREATE PROCEDURE testProc7(@endDate DATE, @startDate DATE = NULL) AS
BEGIN
   SET NOCOUNT ON
   SET @startDate = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
END
GO

-- Explicitly use DATEADD on a DATETIME with implicit CAST for comparison with SaleDate.
-- Based on the answer from Mikael Eriksson.
CREATE PROCEDURE testProcA(@endDateTime DATETIME) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN DATEADD(DAY, -1, @endDateTime) AND @endDateTime
END
GO

-- Explicitly use DATEADD on a DATETIME but CAST to DATE for comparison with SaleDate.
-- Based on the answer from Mikael Eriksson.
CREATE PROCEDURE testProcB(@endDateTime DATETIME) AS
BEGIN
   SET NOCOUNT ON
   SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN CAST(DATEADD(DAY, -1, @endDateTime) AS DATE) AND CAST(@endDateTime AS DATE)
END
GO

-- Use a date lookup table, Sale first.
-- Based on the answer from Kenneth Fisher.
CREATE PROCEDURE testProcC(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM Sale J INNER JOIN testDimDate D ON D.DateKey = J.SaleDate WHERE D.DateKey BETWEEN @startDate AND @endDate
END
GO

-- Use a date lookup table, Sale last.
-- Based on the answer from Kenneth Fisher.
CREATE PROCEDURE testProcD(@endDate DATE) AS
BEGIN
   SET NOCOUNT ON
   DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)
   SELECT SUM(Value) FROM testDimDate D INNER JOIN Sale J ON J.SaleDate = D.DateKey WHERE D.DateKey BETWEEN @startDate AND @endDate
END
GO

------ TEST ------

SET STATISTICS TIME OFF

DECLARE @endDate DATE = '2012-12-10'
DECLARE @startDate DATE = DATEADD(DAY, -1, @endDate)

DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

RAISERROR('Run 1: NoProc with constants', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN '2012-12-09' AND '2012-12-10'
SET STATISTICS TIME OFF

RAISERROR('Run 2: NoProc with constants', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN '2012-12-09' AND '2012-12-10'
SET STATISTICS TIME OFF

DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

RAISERROR('Run 1: NoProc with variables', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
SET STATISTICS TIME OFF

RAISERROR('Run 2: NoProc with variables', 0, 0) WITH NOWAIT
SET STATISTICS TIME ON
SELECT SUM(Value) FROM Sale WHERE SaleDate BETWEEN @startDate AND @endDate
SET STATISTICS TIME OFF

DECLARE @sql NVARCHAR(4000)

DECLARE _cursor CURSOR LOCAL FAST_FORWARD FOR
   SELECT
      procedures.name,
      procedures.object_id
   FROM sys.procedures
   WHERE procedures.name LIKE 'testProc_'
   ORDER BY procedures.name ASC

OPEN _cursor

DECLARE @name SYSNAME
DECLARE @object_id INT

FETCH NEXT FROM _cursor INTO @name, @object_id
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sql = CASE (SELECT COUNT(*) FROM sys.parameters WHERE object_id = @object_id)
      WHEN 0 THEN @name
      WHEN 1 THEN @name + ' ''@endDate'''
      WHEN 2 THEN @name + ' ''@startDate'', ''@endDate'''
   END

   SET @sql = REPLACE(@sql, '@name', @name)
   SET @sql = REPLACE(@sql, '@startDate', CONVERT(NVARCHAR(10), @startDate, 126))
   SET @sql = REPLACE(@sql, '@endDate', CONVERT(NVARCHAR(10), @endDate, 126))

   DBCC FREEPROCCACHE WITH NO_INFOMSGS
   DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

   RAISERROR('Run 1: %s', 0, 0, @sql) WITH NOWAIT
   SET STATISTICS TIME ON
   EXEC sp_executesql @sql
   SET STATISTICS TIME OFF

   RAISERROR('Run 2: %s', 0, 0, @sql) WITH NOWAIT
   SET STATISTICS TIME ON
   EXEC sp_executesql @sql
   SET STATISTICS TIME OFF

   FETCH NEXT FROM _cursor INTO @name, @object_id
END

CLOSE _cursor
DEALLOCATE _cursor

Community · Accepted Answer · 2017-05-23 12:40:44Z

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.

Kenneth Fisher · Accepted Answer · 2013-05-16 14:46:38Z

Ok, I have two possible solutions for you.

First I'm wondering if this will allow for increased parameterization. I haven't had a chance to test it out but it might work.

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

The other option takes advantage of the fact you are using fixed time frames. First create a DateLookup table. Something like this

CurrentDate    8WeekStartDate    8WeekEndDate    etc

Fill it in for every date between now and the next century. This is only ~36500 rows so a fairly small table. Then change your query like this

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

Obviously this is just an example and could certainly be written better but I've had a lot of luck with this type of table. Particularly since it is a static table and can be indexed like crazy.

Dai · Accepted Answer · 2020-07-02 21:57:30Z

(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 :/

Stack Exchange Network

How to avoid using variables in WHERE clause

3 Answers 3

Fantasy time:

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008
or ask your own question.

Linked

Hot Network Questions

How to avoid using variables in WHERE clause

3 Answers 3

Fantasy time:

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008 or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008
or ask your own question.