SQL Server中如何将多个插入存储过程整合到统一存储过程并正确调用

Alright, let's fix your unified stored procedure step by step. There are a few key issues with your current implementation that are causing it to fail, plus some missing pieces to handle your table's foreign key relationships properly.

First, let's break down the problems in your original code: #

• Incorrect stored procedure call syntax: You can't use parentheses () or square brackets [] to pass parameters when executing a procedure. The correct syntax is EXEC ProcedureName @Param1, @Param2.
• Misplaced SCOPE_IDENTITY(): In your InsertMember procedure, you tried to get the MemberID before inserting the record—this will always return NULL because no identity value has been generated yet.
• Missing foreign key handling: Your tables rely on foreign key relationships (e.g., MemberCard needs a MemberID, MemberDetails needs a MemberCardID), but your small procedures don't account for this, leading to constraint violations.
• Fragmented transactions: Each small procedure has its own transaction, but you want the entire member creation flow to be atomic—either all steps succeed, or none do.

Step 1: Update your small procedures to return inserted IDs #

We'll add OUTPUT parameters to each procedure so we can pass generated identity values between steps for foreign key links.

Updated InsertMember

CREATE PROCEDURE InsertMember 
    @MFirstName varchar(60), 
    @MLastName varchar(60),
    @MemberID INT OUTPUT -- Return the generated MemberID
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        INSERT INTO Member(MFirstName, MLastName, MDateJoined) 
        VALUES (@MFirstName, @MLastName, GETDATE());
        
        SET @MemberID = SCOPE_IDENTITY(); -- Get ID AFTER insertion
        
        COMMIT TRAN;
        PRINT 'Member Inserted Successfully';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        PRINT 'ERROR Inserting Member';
        SELECT ERROR_MESSAGE() AS ErrorMessage;
        THROW; -- Propagate error to parent procedure
    END CATCH
END
GO

New InsertMemberCard (required for MemberDetails foreign key)

Since MemberDetails depends on MemberCard, we need this procedure to link the card to the member:

CREATE PROCEDURE InsertMemberCard
    @MemberID INT,
    @MemberCardID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        -- Fill in default values for required fields (adjust as needed)
        INSERT INTO MemberCard(MemberID, MActiveORInactive, IssueDate, NoOfCards)
        VALUES (@MemberID, 'A', GETDATE(), 1);
        
        SET @MemberCardID = SCOPE_IDENTITY();
        
        COMMIT TRAN;
        PRINT 'MemberCard Inserted Successfully';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        PRINT 'ERROR Inserting MemberCard';
        SELECT ERROR_MESSAGE() AS ErrorMessage;
        THROW;
    END CATCH
END
GO

Updated InsertMemberDetails

CREATE procedure InsertMemberDetails 
    @MemberCardID INT, -- Link to MemberCard
    @CMAddress varchar(100), 
    @CMCity varchar (50), 
    @CMEmail varchar (100), 
    @CMPhone int,
    @MemberDetailsID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        INSERT INTO MemberDetails(MemberCardID, CMAddress, CMCity, CMEmail, CMPhone) 
        VALUES (@MemberCardID, @CMAddress, @CMCity, @CMEmail, @CMPhone);
        
        SET @MemberDetailsID = SCOPE_IDENTITY();
        
        COMMIT TRAN;
        PRINT 'MemberDetails Inserted Successfully';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        PRINT 'ERROR Inserting MemberDetails!!!';
        SELECT ERROR_MESSAGE() AS ErrorMessage;
        THROW;
    END CATCH
END
GO

Updated InsertEmergencyContact

Note: Your EmergencyContact table links to MemberHistory, so we'll assume you have an InsertMemberHistory procedure (add it if missing) to get the required MemberHistoryID:

-- First, create InsertMemberHistory (adjust fields to match your table)
CREATE PROCEDURE InsertMemberHistory
    @MemberID INT,
    @MemberHistoryID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        INSERT INTO MemberHistory(MemberID, CreatedDate) -- Add your actual fields
        VALUES (@MemberID, GETDATE());
        
        SET @MemberHistoryID = SCOPE_IDENTITY();
        
        COMMIT TRAN;
        PRINT 'MemberHistory Inserted Successfully';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        PRINT 'ERROR Inserting MemberHistory';
        SELECT ERROR_MESSAGE() AS ErrorMessage;
        THROW;
    END CATCH
END
GO

-- Updated InsertEmergencyContact
CREATE procedure InsertEmergencyContact 
    @MemberHistoryID INT, -- Link to MemberHistory
    @CECFirstName varchar(60), 
    @CECLastName varchar (60), 
    @CECPhone int , 
    @CECAddress varchar (100),
    @CECID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        INSERT INTO EmergencyContact(MemberHistoryID, CECFirstName, CECLastName, CECPhone, CECAddress, CECDateUpdated) 
        VALUES (@MemberHistoryID, @CECFirstName, @CECLastName, @CECPhone, @CECAddress, GETDATE());
        
        SET @CECID = SCOPE_IDENTITY();
        
        COMMIT TRAN;
        PRINT 'Emergency Contact Details Inserted Successfully';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        PRINT 'ERROR Inserting Emergency Contact Details!!!';
        SELECT ERROR_MESSAGE() AS ErrorMessage;
        THROW;
    END CATCH
END
GO

Step 2: Create the unified stored procedure #

This procedure will orchestrate all steps in a single transaction, ensuring data consistency:

CREATE procedure Proc_InsertNewMember 
    @MFirstName varchar(60), 
    @MLastName varchar(60), 
    @CMAddress varchar(100), 
    @CMCity varchar(50), 
    @CMPhone int , 
    @CMEmail varchar(100), 
    @CECFirstName varchar(60), 
    @CECLastName varchar (60), 
    @CECPhone int, 
    @CECAddress Varchar (100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE 
        @MemberID INT,
        @MemberCardID INT,
        @MemberDetailsID INT,
        @MemberHistoryID INT,
        @CECID INT;
    
    BEGIN TRY
        BEGIN TRAN;
        
        -- 1. Insert Member and get ID
        EXEC InsertMember @MFirstName = @MFirstName, @MLastName = @MLastName, @MemberID = @MemberID OUTPUT;
        
        -- 2. Insert MemberCard linked to Member
        EXEC InsertMemberCard @MemberID = @MemberID, @MemberCardID = @MemberCardID OUTPUT;
        
        -- 3. Insert MemberDetails linked to MemberCard
        EXEC InsertMemberDetails @MemberCardID = @MemberCardID, @CMAddress = @CMAddress, @CMCity = @CMCity, 
            @CMEmail = @CMEmail, @CMPhone = @CMPhone, @MemberDetailsID = @MemberDetailsID OUTPUT;
        
        -- 4. Insert MemberHistory linked to Member
        EXEC InsertMemberHistory @MemberID = @MemberID, @MemberHistoryID = @MemberHistoryID OUTPUT;
        
        -- 5. Insert EmergencyContact linked to MemberHistory
        EXEC InsertEmergencyContact @MemberHistoryID = @MemberHistoryID, @CECFirstName = @CECFirstName, 
            @CECLastName = @CECLastName, @CECPhone = @CECPhone, @CECAddress = @CECAddress, @CECID = @CECID OUTPUT;
        
        COMMIT TRAN;
        PRINT 'All records inserted successfully!';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        PRINT 'ERROR occurred during insertion process';
        SELECT ERROR_MESSAGE() AS ErrorMessage;
        THROW;
    END CATCH
END
GO

Step 3: Test the unified procedure #

You can now run your desired single-line call (note we removed @CECDateUpdated since we use GETDATE() in the procedure):

EXEC Proc_InsertNewMember 
    @MFirstName = 'Michael', 
    @MLastName = 'Goodwin', 
    @CMAddress = 'Za Cool Street', 
    @CMCity = 'Limerick', 
    @CMPhone = 7843273, 
    @CMEmail = 'dhajsdhjas@dsjai.com', 
    @CECFirstName = 'Rachel', 
    @CECLastName = 'Green', 
    @CECPhone = 2121321, 
    @CECAddress = 'The other cool street';

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