Azure管道调用带表参数的存储过程修改求助

Hey there! As someone who's inherited an undocumented Azure project, I feel your pain—let's get this sorted out. The key issue here is that you've updated your target table and datasets, but you haven't synchronized the table-valued parameter type your stored procedure relies on. Here's a step-by-step fix:

1. Update the Table-Valued Type ([dbo].[InputTable]) #

Your stored procedure accepts @azure_input_table as a parameter of type [dbo].[InputTable]. If you added a new column to your target table, this table type needs to include the same new column too—otherwise, the parameter can't carry the new column's data, which triggers errors.

Run this SQL to alter the table type:

ALTER TYPE [dbo].[InputTable] ADD COLUMN new_column [YourColumnDataType] NULL; -- Use NOT NULL if the column requires a value

Replace [YourColumnDataType] with the actual data type of your new column (e.g., VARCHAR(100), DATETIME2, INT).

2. Fix the Stored Procedure Syntax #

Your current code uses placeholder text (columns) which isn't valid SQL. You need to explicitly list all columns (including the new one) in both the INSERT and SELECT clauses to match the updated schema.

Here's the corrected stored procedure code:

ALTER PROCEDURE [dbo].[spLoad]
    @azure_input_table [dbo].[InputTable] READONLY
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra result sets that can break pipeline integrations

    INSERT INTO [dbo].[Table] (column1, column2, new_column) -- List ALL existing columns + your new column
    SELECT column1, column2, new_column -- Match the columns exactly from the table parameter
    FROM @azure_input_table;
END

Replace column1, column2 with the actual existing column names in your target table and table type.

3. Double-Check the Azure Pipeline #

Don't skip this step! Ensure the Azure Pipeline that calls this stored procedure is now passing data that includes the new column in the table parameter. If the pipeline is still sending the old schema, you'll run into mismatched column errors even after fixing the SQL.

Why This Was Breaking #

When you added a new column to your target table but didn't update the table-valued type, the @azure_input_table parameter didn't have that column defined. SQL Server throws an error because it can't find the column in the parameter's schema. Updating the table type first aligns the input schema with your target table, making the insert work as expected.

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