SQL Server存储过程中含NULL的子串匹配参数过滤最佳方案咨询

Hey there! Let's break down how to fix this issue properly. From what you've described, the core problem is likely that your current WHERE condition doesn't handle the NULL parameter case correctly, or isn't accounting for substring matching when the parameter has a value. Let's walk through the best approaches.

Why Your Current Condition Isn't Working #

Chances are, you're using something like this initially:

WHERE contract_no LIKE @ContractParam

This fails for two key reasons:

1. When @ContractParam is NULL, any comparison with NULL returns UNKNOWN, so no rows are matched at all.
2. When you pass a substring (e.g., "ABC"), you aren't wrapping it in wildcard characters (%), so it only matches exact contract numbers instead of those containing the substring.

Core Solution: Handle NULL and Substring Matching #

The most straightforward and readable approach is to use a conditional WHERE clause that covers both cases:

Basic Implementation (Works for Most Databases) #

SELECT *
FROM YourContractTable
WHERE 
    -- If parameter is NULL, skip filtering; otherwise, match substrings
    @ContractParam IS NULL 
    OR contract_no LIKE '%' + @ContractParam + '%'

• Note for MySQL/Oracle: Use database-specific string concatenation syntax instead of +:

  -- MySQL
  OR contract_no LIKE CONCAT('%', @ContractParam, '%')
  
  -- Oracle
  OR contract_no LIKE '%' || @ContractParam || '%'
  

Alternative with COALESCE (Shorter, Less Explicit) #

If you prefer a more concise (though slightly less readable) version, you can use COALESCE:

SELECT *
FROM YourContractTable
WHERE contract_no LIKE COALESCE('%' + @ContractParam + '%', contract_no)

When @ContractParam is NULL, COALESCE returns contract_no, so the condition becomes contract_no LIKE contract_no (always true, effectively skipping the filter).

Performance Optimizations for Large Datasets #

The above solutions work great for small to medium tables, but if you're dealing with large datasets, LIKE '%substring%' will bypass standard indexes (since the wildcard is at the start). Here are better options:

1. Use Full-Text Search #

Most databases support full-text indexing, which is optimized for substring/keyword searches. For example:

• SQL Server:

  WHERE CONTAINS(contract_no, @ContractParam)
  

  (Requires a full-text index on contract_no)
• MySQL:

  WHERE MATCH(contract_no) AGAINST(@ContractParam IN BOOLEAN MODE)
  

2. Dynamic SQL (Avoids Parameter Sniffing) #

If full-text search isn't an option, dynamic SQL lets you build the query conditionally, which can lead to better execution plans:

DECLARE @SQL NVARCHAR(MAX) = N'
SELECT * FROM YourContractTable
WHERE 1=1'

IF @ContractParam IS NOT NULL
BEGIN
    SET @SQL += N' AND contract_no LIKE ''%'' + @ContractParam + ''%'''
END

-- Execute with parameterization to prevent SQL injection
EXEC sp_executesql @SQL, N'@ContractParam VARCHAR(100)', @ContractParam = @ContractParam

This way, when @ContractParam is NULL, the query runs without the filter clause entirely, avoiding any unnecessary logic.

3. Adjust to Prefix Matching (If Possible) #

If your business allows matching only the start of contract numbers (e.g., "ABC%" instead of "%ABC%"), you can use a standard B-tree index:

WHERE 
    @ContractParam IS NULL 
    OR contract_no LIKE @ContractParam + '%'

This will leverage the index on contract_no for fast lookups.

Key Checks to Ensure Success #

• Verify that your UI is actually passing NULL when the field is empty (not an empty string ''). If it passes an empty string, update the condition to handle that too:

  WHERE 
      (@ContractParam IS NULL OR @ContractParam = '')
      OR contract_no LIKE '%' + @ContractParam + '%'
  

• Ensure @ContractParam is defined as a string type (e.g., VARCHAR(100)) with enough length to hold the longest possible substring.

内容的提问来源于stack exchange，提问作者Constantin Cabiniuc