SQL Server存储过程调整：基于@HasCreditMemoNo参数筛选CreditMemoNumber列

Hey there! Let's refine this stored procedure to meet your exact filtering needs clearly and efficiently. Here's an improved version that covers all three scenarios you specified, with added best practices for SQL Server:

ALTER PROCEDURE GetVendor_RMA_CreditMemo 
    @HasCreditMemoNo INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra "rows affected" messages from cluttering results

    SELECT 
        CreditMemoNumber,
        -- Calculate HasCreditMemoNo directly in the select
        CASE 
            WHEN ISNULL(CreditMemoNumber, '') != '' THEN 1 
            ELSE 0 
        END AS HasCreditMemoNo
    FROM XYZ
    WHERE 
        -- Return all rows when parameter is -1
        (@HasCreditMemoNo = -1)
        -- Filter for rows with no CreditMemoNumber (covers NULL and empty string)
        OR (@HasCreditMemoNo = 0 AND ISNULL(CreditMemoNumber, '') = '')
        -- Filter for rows with non-empty CreditMemoNumber
        OR (@HasCreditMemoNo = 1 AND ISNULL(CreditMemoNumber, '') != '');
END

Key Improvements & Explanations: #

• Removed redundant subquery: There's no need to wrap the select in a subquery—we can apply the filtering logic directly in the WHERE clause, making the code cleaner and more efficient.
• Handled NULL values: Since CreditMemoNumber is a varchar, "no value" often includes both empty strings ('') and NULL. Using ISNULL(CreditMemoNumber, '') converts NULLs to empty strings, so we can check both cases in one go. If your business logic only considers empty strings as "no value", you can remove the ISNULL and just use CreditMemoNumber = ''.
• Added SET NOCOUNT ON: This is a standard best practice for SQL Server stored procedures—it stops the server from returning extra "X rows affected" messages, which can cause issues with application code that expects only the result set.
• Clear condition breakdown: Each parameter case is explicitly called out in the WHERE clause, making it easy to read and modify later if your requirements change.

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