存储过程传参无数据写入求助：手动赋值正常传参失效

First, let's recap your issue: you've built the [OPTI].[Refreshfinal_GetAllActions] stored procedure to migrate data from an external table to your stage table. When you run the raw SQL with hardcoded parameter values, it works perfectly—but when you call the procedure with parameters, it returns 0 rows affected even though matching data exists in the external table.

Here are the most likely culprits and how to fix them:

1. Undefined VARCHAR Parameter Lengths #

This is the #1 cause of this kind of problem. When you declare @RunDate [Varchar] and @CN [Varchar] without specifying a length, SQL Server defaults the length to 1 character. That means any parameter longer than 1 character gets truncated—so if you pass '2024-05-20' for @RunDate, it gets chopped down to '2', which obviously won't match your table data.

Fix: Define explicit lengths for your parameters that match the data in your tables:

ALTER PROC [OPTI].[Refreshfinal_GetAllActions] 
    @RunDate VARCHAR(10), -- Match your Input_Date column length (e.g., 'YYYY-MM-DD' is 10 chars)
    @CN VARCHAR(50) -- Adjust based on your Country column's max length
AS 
BEGIN
    -- Rest of your code...
END

2. Hidden Whitespace in Parameters #

When passing parameters, it's easy to accidentally include leading/trailing spaces (especially if the input comes from an app or user input). Your manual query uses hardcoded values without spaces, so it matches—but the procedure's parameters might have extra whitespace that doesn't match the Country or Input_Date values in your table.

Fix: Trim the parameters at the start of the procedure to eliminate any unwanted whitespace:

ALTER PROC [OPTI].[Refreshfinal_GetAllActions] 
    @RunDate VARCHAR(10),
    @CN VARCHAR(50)
AS 
BEGIN
    -- Clean up parameters first
    SET @RunDate = LTRIM(RTRIM(@RunDate));
    SET @CN = LTRIM(RTRIM(@CN));

    BEGIN TRAN
        PRINT 'Deleting records from Stage_GetAllActions'
        DELETE FROM Opti.[Stage_GetAllActions] 
        WHERE Input_Date = @RunDate AND Country = @CN

        PRINT 'Inserting records into Stage_GetAllActions'
        INSERT INTO Opti.[Stage_GetAllActions] 
        SELECT DISTINCT 
            CASE WHEN LTRIM(RTRIM(Country)) = '' THEN NULL ELSE Country END,
            CASE WHEN LTRIM(RTRIM(Etl_Batch)) = '' THEN NULL ELSE Etl_Batch END,
            CASE WHEN LTRIM(RTRIM(Input_Date)) = '' THEN NULL ELSE Input_Date END,
            CASE WHEN LTRIM(RTRIM(ActionID)) = '' THEN NULL ELSE ActionID END,
            CASE WHEN LTRIM(RTRIM(ActionName)) = '' THEN NULL ELSE ActionName END,
            CASE WHEN LTRIM(RTRIM(Api_Executed_Datetime)) = '' THEN NULL ELSE Api_Executed_Datetime END
        FROM [Opti].[Ext_Stage_GetAllActions] 
        WHERE Input_Date = @RunDate AND Country = @CN;
    COMMIT TRAN
END

3. Case Sensitivity Mismatch #

If your database uses a case-sensitive collation (e.g., SQL_Latin1_General_CP1_CS_AS), a value like 'US' in your table won't match 'us' passed as a parameter. Your manual query uses the exact case from the table, so it works—but the procedure's parameter might be using a different case.

Fix: Normalize the case in your WHERE clause to avoid this:

WHERE UPPER(Input_Date) = UPPER(@RunDate) AND UPPER(Country) = UPPER(@CN)

Or ensure the parameters are passed in the same case as the table data.

4. Debugging Step: Verify Parameter Values #

To confirm exactly what's being passed to the procedure, add print statements to log the parameter values. This will help you spot truncation, whitespace, or case issues:

ALTER PROC [OPTI].[Refreshfinal_GetAllActions] 
    @RunDate VARCHAR(10),
    @CN VARCHAR(50)
AS 
BEGIN
    -- Debug: Print the exact parameter values received
    PRINT 'Received RunDate: ''' + @RunDate + '''';
    PRINT 'Received Country: ''' + @CN + '''';

    SET @RunDate = LTRIM(RTRIM(@RunDate));
    SET @CN = LTRIM(RTRIM(@CN));

    -- Rest of your code...
END

When you run the procedure, check the Messages tab in SSMS to see if the printed values match what you expected to pass.

Bonus: Test the Manual Query with Procedure Parameter Values #

Take the exact values printed from the debug step and plug them into your manual SELECT query. If that query also returns 0 rows, the issue is with the parameter values themselves—not the procedure. If it returns rows, then you know the problem is in how the procedure is handling the parameters.

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