T-Lift: dynamic SQL without writing it by hand.

T-Lift renders a catch-all procedure

The problem: one query for every case

Search forms with many optional filters often end up in SQL Server following this pattern:

CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @CustomerID INT = NULL,
    @Status NVARCHAR(20) = NULL
AS
SELECT o.OrderID, o.CustomerID, o.OrderDate, o.Status
FROM dbo.Orders o
WHERE (@CustomerID IS NULL OR o.CustomerID = @CustomerID)
  AND (@Status IS NULL OR o.Status = @Status)

The result is always correct. But the optimizer has to cover every combination of set and empty parameters at the same time. Which plan ends up in the cache is decided by the first call. A plan built for a search by one customer is then reused for the query without any filter, and the number of reads can differ by orders of magnitude. This is parameter sniffing. It is not a bug but simply how the plan cache works. There is no index that fixes this structurally; the problem lies in how the query is written.

What parameter sniffing looks like in a plan, with real measurements →

The known workarounds and their cost

How T-Lift works

You write your procedure as usual and add directives as comments (--#). Because they are only comments, the procedure stays fully executable: you develop, test and debug in SSMS as always. Once everything works, T-Lift renders the dynamic version from it.

                                        --#[
SELECT o.OrderID, o.CustomerID, o.OrderDate, o.Status
FROM dbo.Orders o
WHERE                                   --#if @CustomerID IS NOT NULL OR @Status IS NOT NULL
(                                       --#-
@CustomerID IS NULL OR                  --#-
o.CustomerID = @CustomerID              --#if @CustomerID IS NOT NULL
)                                       --#-
AND                                     --#if @CustomerID IS NOT NULL AND @Status IS NOT NULL
(                                       --#-
@Status IS NULL OR                      --#-
o.Status = @Status                      --#if @Status IS NOT NULL
)                                       --#-
                                        --#]

The result contains only the conditions that are actually needed in the respective call. Each parameter combination gets its own reusable plan:

IF @CustomerID IS NOT NULL
    SET @sql = @sql + 'o.CustomerID = @CustomerID' + CHAR(13)+CHAR(10)
...
EXEC sp_executesql @sql,
     N'@CustomerID int, @Status nvarchar(20)', @CustomerID, @Status

What else is worth noting

• Always parameterized.
  T-Lift generates sp_executesql with a correctly typed parameter list taken from sys.parameters. Parameter values are never built into the SQL string. This is safer than a lot of hand-written dynamic SQL.
• Buckets for the plan cache.
  With --#buckets, each value range of a parameter gets its own plan, for example small versus large amounts.
• Safe dynamic ORDER BY.
  --#sort allows only approved columns. The classic entry point for SQL injection when sorting stays closed.
• Help for legacy code.
  @suggest = 1 scans existing procedures for catch-all patterns and proposes suitable annotations.
• No forgotten rendering.
  Every rendered procedure carries a stamp with the source hash. @checkDrift reports when the source has changed and the rendered version is out of date.
• Check before rendering.
  @validateOnly finds unclosed brackets, typos in directives and conditions outside a section.

EXEC dbo.sp_tlift
    @DatabaseName  = 'YourDatabase',
    @ProcedureName = 'LegacyProcedure',
    @suggest       = 1;   -- finds catch-all patterns and proposes annotations

Limitations you should know about

• Permissions: Like any dynamic SQL, T-Lift breaks the ownership chain. Users need read permissions on the tables, or the procedure runs with EXECUTE AS OWNER or through module signing.
• Not always faster: T-Lift pays off mainly for recurring, frequently executed query shapes where the compile cost of OPTION (RECOMPILE) matters. It is not a cure-all.
• Minimum version: SQL Server 2017, with no CLR and no external dependencies.

Try it out

Download sp_tlift.sql from the repository and install it in a separate helper database. EXEC dbo.sp_tlift @help = 1; explains all options. The repository also contains demos with realistic business queries and a script that compares T-Lift and OPTION (RECOMPILE) using Query Store data.

Planned next are, among other things, bucket boundaries derived from statistics histograms and feedback from Query Store that reports plan regressions of rendered procedures. Questions, ideas and contributions are welcome through GitHub issues. A more detailed introduction with background is available on Medium.