存储过程IN子句使用参数导致更新执行但未生效的问题排查

Ah, I spot the problem right away! When you pass a string like 'AC101','AC102','AC103' into the @ReciptNo parameter and use it directly in the IN clause, SQL Server treats that entire string as a single literal value—not a list of separate receipt numbers. So instead of looking for rows where Recipt_No is AC101, AC102, or AC103, it's searching for a row where Recipt_No exactly matches the full string 'AC101','AC102','AC103' (quotes and all). That's why your procedure runs without errors but doesn't update any rows.

Here are two solid solutions, ordered by best practice:

1. Use a Table-Valued Parameter (Recommended) #

This is the safest and most efficient approach, especially for avoiding SQL injection and handling lists of values cleanly.

Step 1: Create a User-Defined Table Type #

First, define a table type that matches the structure of your receipt number list:

CREATE TYPE dbo.ReceiptNumberList AS TABLE (ReceiptNo NVARCHAR(50));
GO

Step 2: Modify Your Stored Procedure #

Update the procedure to accept this table type as a parameter, then query against it in the IN clause:

ALTER PROCEDURE YourStoredProcedureName -- Replace with your actual procedure name
    @PaymentDate NVARCHAR(MAX),
    @ReciptNo dbo.ReceiptNumberList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE Monthly_Payment
    SET Payment_Date = @PaymentDate
    WHERE Recipt_No IN (SELECT ReceiptNo FROM @ReciptNo);
END
GO

Step 3: Call the Procedure from Your Code #

Instead of passing a single string, pass a collection of receipt numbers as a table. Here's an example in C#:

using (var connection = new SqlConnection("YourDatabaseConnectionString"))
{
    connection.Open();
    using (var command = new SqlCommand("YourStoredProcedureName", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        
        // Add the PaymentDate parameter
        command.Parameters.Add("@PaymentDate", SqlDbType.NVarChar).Value = "2024-05-20";
        
        // Create a DataTable to hold receipt numbers
        var receiptTable = new DataTable();
        receiptTable.Columns.Add("ReceiptNo", typeof(string));
        receiptTable.Rows.Add("AC101");
        receiptTable.Rows.Add("AC102");
        receiptTable.Rows.Add("AC103");
        
        // Add the table-valued parameter
        var tvpParameter = command.Parameters.AddWithValue("@ReciptNo", receiptTable);
        tvpParameter.SqlDbType = SqlDbType.Structured;
        tvpParameter.TypeName = "dbo.ReceiptNumberList";
        
        // Execute the update
        command.ExecuteNonQuery();
    }
}

2. Use Dynamic SQL (Less Secure, But Quick Fix) #

If you can't use a table-valued parameter for some reason, dynamic SQL will work—but be extremely careful if the @ReciptNo value comes from user input (it's vulnerable to SQL injection). Here's how to implement it safely with parameterization:

ALTER PROCEDURE YourStoredProcedureName
    @PaymentDate NVARCHAR(MAX),
    @ReciptNo NVARCHAR(MAX) -- Increase the length to MAX to accommodate longer lists
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @SQLQuery NVARCHAR(MAX);
    
    -- Build the dynamic SQL, keeping @PaymentDate parameterized to avoid injection
    SET @SQLQuery = N'UPDATE Monthly_Payment
                      SET Payment_Date = @PaymentDate
                      WHERE Recipt_No IN (' + @ReciptNo + N')';
    
    -- Execute the query with the parameterized @PaymentDate
    EXEC sp_executesql @SQLQuery, N'@PaymentDate NVARCHAR(MAX)', @PaymentDate = @PaymentDate;
END
GO

Important Note: #

Only use the dynamic SQL approach if you're 100% sure the @ReciptNo string is generated safely (no user input). Table-valued parameters are always the better choice for this scenario.

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