SQL Server中利用必填字段表生成WHERE...IS NULL语句的可行性咨询

Absolutely! You can absolutely build dynamic WHERE ... IS NULL checks based on your Required_Fields_Table in SQL Server—this is a smart way to keep your validation logic centralized instead of hardcoding it into every query, especially perfect for your scenario where required field rules vary. Let me walk you through how to implement this.

First, let’s assume your Required_Fields_Table has at minimum these columns (adjust if your schema differs):

• FieldName: The name of the column in Transactions that’s required
• (Optional) TransactionTypeID: If you have different required rules for different transaction types (since you mentioned rule differences)
• KeyField: The primary/foreign key column that links Required_Fields_Table to Transactions (e.g., TransactionID)

Basic Dynamic Check: Find All Transactions with Missing Required Fields #

This example generates a query that returns any Transactions record where at least one required field is NULL. We’ll use STRING_AGG (available in SQL Server 2017+) to dynamically build our WHERE clause:

DECLARE @WhereClause NVARCHAR(MAX);
DECLARE @FullQuery NVARCHAR(MAX);

-- Build the list of "field IS NULL" conditions joined with OR
SELECT @WhereClause = STRING_AGG(QUOTENAME(FieldName) + ' IS NULL', ' OR ')
FROM Required_Fields_Table;

-- Assemble the full query
SET @FullQuery = N'SELECT t.*
FROM Transactions t
WHERE ' + @WhereClause;

-- Execute the dynamic SQL
EXEC sp_executesql @FullQuery;

Handling Rule Differences (Per Transaction Type) #

If your required fields vary by transaction type, just add a filter to the Required_Fields_Table query to target specific rules:

DECLARE @TargetTransactionType INT = 2; -- Replace with your target type ID
DECLARE @WhereClause NVARCHAR(MAX);
DECLARE @FullQuery NVARCHAR(MAX);

-- Only pull required fields for the target transaction type
SELECT @WhereClause = STRING_AGG(QUOTENAME(FieldName) + ' IS NULL', ' OR ')
FROM Required_Fields_Table
WHERE TransactionTypeID = @TargetTransactionType;

-- Build query that filters for the target type AND checks missing fields
SET @FullQuery = N'SELECT t.*
FROM Transactions t
WHERE t.TransactionTypeID = @TargetType
AND (' + @WhereClause + ')';

-- Execute with parameter to avoid SQL injection
EXEC sp_executesql @FullQuery, N'@TargetType INT', @TargetType = @TargetTransactionType;

Bonus: Identify Which Fields Are Missing #

If you want to see exactly which required fields are NULL for each record, extend the dynamic query to include flags for each missing field:

DECLARE @FieldChecks NVARCHAR(MAX);
DECLARE @ExistsCheck NVARCHAR(MAX);
DECLARE @FullQuery NVARCHAR(MAX);

-- Build CASE statements to flag missing fields
SELECT @FieldChecks = STRING_AGG(
    N'CASE WHEN ' + QUOTENAME(FieldName) + ' IS NULL THEN ''' + FieldName + ''' ELSE NULL END AS Missing_' + FieldName,
    N', '
)
FROM Required_Fields_Table;

-- Build the EXISTS condition to filter only records with missing fields
SELECT @ExistsCheck = STRING_AGG(N't.' + QUOTENAME(FieldName) + ' IS NULL', N' OR ')
FROM Required_Fields_Table;

-- Assemble the full query
SET @FullQuery = N'SELECT t.*, ' + @FieldChecks + '
FROM Transactions t
WHERE EXISTS (
    SELECT 1
    WHERE ' + @ExistsCheck + '
)';

-- Execute
EXEC sp_executesql @FullQuery;

Key Notes: #

• Use QUOTENAME() to safely handle field names with special characters and prevent SQL injection.
• If you’re on a pre-2017 SQL Server version, replace STRING_AGG with FOR XML PATH to concatenate the conditions (just ask if you need that syntax!).
• This approach keeps your validation logic flexible—update Required_Fields_Table instead of rewriting queries when rules change.

内容的提问来源于stack exchange，提问作者Andrew Suh