存储过程传全参数正常，仅传ID返回空结果集，请求排查

Hey there! Let's figure out why your stored procedure is returning an empty result set when you only pass the ID parameter, even though it works fine when you provide all parameters. This is a super common issue, and it almost always boils down to how your WHERE clause handles optional parameters.

1. Check how optional parameters are handled in your WHERE clause #

The most likely culprit is that your WHERE clause is treating optional parameters as required, even when they're not provided. For example, if your procedure has a clause like this:

WHERE ID = @ID
  AND CustomerName = @CustomerName
  AND OrderDate = @OrderDate

When you don't pass @CustomerName or @OrderDate, they might default to NULL (or maybe an empty string), and in SQL, Column = NULL always evaluates to unknown (which gets treated as false). That means even if the ID matches, the other conditions are filtering out all rows.

The fix here is to adjust each optional parameter's condition to ignore it when the parameter is NULL:

WHERE ID = @ID
  AND (@CustomerName IS NULL OR CustomerName = @CustomerName)
  AND (@OrderDate IS NULL OR OrderDate = @OrderDate)

This way, if @CustomerName is NULL (because you didn't pass it), that part of the condition is skipped entirely, and only the ID check applies.

2. Verify your parameter default values #

Make sure your optional parameters are actually set to default to NULL when not provided. If you defined them with a default like '' (empty string) or 0, that will cause issues. For example:

-- Bad: Defaults to empty string, which won't match most rows
CREATE PROCEDURE GetOrders
  @ID INT,
  @CustomerName VARCHAR(100) = '',
  @OrderDate DATE = '1900-01-01'

Instead, set them to NULL:

-- Good: Optional parameters default to NULL
CREATE PROCEDURE GetOrders
  @ID INT,
  @CustomerName VARCHAR(100) = NULL,
  @OrderDate DATE = NULL

3. Look for conditional logic that might override the ID check #

Sometimes stored procedures have IF/ELSE blocks that change the query based on which parameters are provided. For example:

IF @CustomerName IS NOT NULL
BEGIN
  SELECT * FROM Orders WHERE ID = @ID AND CustomerName = @CustomerName
END
ELSE
BEGIN
  -- Oops! This might be querying something else entirely
  SELECT * FROM Orders WHERE OrderDate = GETDATE()
END

Double-check that when only the ID is passed, the procedure is running the correct query that filters solely by ID.

4. Test the generated SQL manually #

To debug further, you can run the equivalent SQL directly in your database with the parameter values you're using. For example, if you call EXEC GetOrders @ID = 123, run:

SELECT * FROM Orders
WHERE ID = 123
  AND (NULL IS NULL OR CustomerName = NULL)
  AND (NULL IS NULL OR OrderDate = NULL)

This will show you exactly what the procedure is executing, and you can see if the WHERE clause is behaving as expected.

Once you fix the WHERE clause logic and ensure optional parameters default to NULL, your procedure should return the correct row when only the ID is passed.

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