求助：含事务处理的Vendor相关存储过程报错排查

Hey there! Let's tackle this stored procedure issue together. Since you only shared the USE... snippet of your code, I can't pinpoint the exact errors right now, but I'll walk you through common pitfalls and share a compliant template—this should help you spot where things might be going wrong.

Common Issues to Check First #

• Transaction & Exception Handling Syntax: It’s easy to mess up the order of TRY/CATCH blocks or forget to validate the transaction state before rolling back. For example, if you don’t check @@TRANCOUNT > 0 in the catch block, you might get errors trying to roll back a transaction that doesn’t exist.
• Mismatched Table Schema: Insert errors often happen when you miss required fields, use the wrong data types, or try to manually assign values to auto-increment columns (like VendorID or InvoiceID).
• Broken Foreign Key Links: When inserting related records (Invoice → Vendor, InvoiceLineItems → Invoice), you need to reliably grab the ID of the newly created parent record. Using @@IDENTITY can lead to wrong IDs if there are triggers on your tables—stick with SCOPE_IDENTITY() instead.
• Vague Error Output: If your catch block doesn’t return detailed error info, you’ll never know exactly which step failed. Always include ERROR_NUMBER(), ERROR_MESSAGE(), and other error functions to get clarity.

Compliant Template to Compare Against #

Here’s a fully functional stored procedure that meets your requirements—replace placeholder column names with your actual table structure:

USE YourDatabaseName; -- Swap this with your database name
GO

CREATE OR ALTER PROCEDURE InsertVendorWithInvoices
    @VendorName NVARCHAR(100),
    @VendorEmail NVARCHAR(100),
    @InvoiceDate DATE,
    @InvoiceTotal DECIMAL(10,2),
    @ItemName NVARCHAR(100),
    @ItemQty INT,
    @ItemPrice DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NewVendorID INT;
    DECLARE @NewInvoiceID INT;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Step 1: Insert new Vendor
        INSERT INTO Vendor (VendorName, VendorEmail) -- Use your actual Vendor table columns
        VALUES (@VendorName, @VendorEmail);
        SET @NewVendorID = SCOPE_IDENTITY(); -- Grab the auto-generated Vendor ID

        -- Step 2: Insert linked Invoice
        INSERT INTO Invoice (VendorID, InvoiceDate, TotalAmount) -- Use your actual Invoice columns
        VALUES (@NewVendorID, @InvoiceDate, @InvoiceTotal);
        SET @NewInvoiceID = SCOPE_IDENTITY(); -- Grab the auto-generated Invoice ID

        -- Step 3: Insert Invoice Line Item
        INSERT INTO InvoiceLineItems (InvoiceID, ItemName, Quantity, UnitPrice) -- Use your actual line item columns
        VALUES (@NewInvoiceID, @ItemName, @ItemQty, @ItemPrice);

        -- Commit if all steps succeed
        COMMIT TRANSACTION;
        PRINT 'Success! New Vendor ID: ' + CAST(@NewVendorID AS NVARCHAR);
    END TRY
    BEGIN CATCH
        -- Roll back only if a transaction is active
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Output detailed error for debugging
        SELECT 
            ERROR_NUMBER() AS ErrorID,
            ERROR_MESSAGE() AS ErrorDetails,
            ERROR_LINE() AS ErrorLineNumber;
        PRINT 'Transaction failed. All changes rolled back.';
    END CATCH
END
GO

Next Steps #

1. Compare your code against this template—look for missing SCOPE_IDENTITY() calls, incorrect transaction handling, or mismatched column names.
2. If you still get errors, share the exact error message (number, text, and line number) and your full stored procedure code. That’ll let me zero in on the problem quickly.

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