sql - 如何将一系列存储过程合并到一个更大的存储过程中

Question

我在 SQL Server 中遇到了障碍，我有一堆小的插入存储过程，我想在创建一个统一过程时调用它们。

我有这些表：

CREATE TABLE [Member] 
(
    [MemberID] Int IDENTITY(1,1),
    [ProgID] int,
    [MemberCardID] int,
    [CECID] int,
    [MFirstName] VarChar (60),
    [MLastName] VarChar (60),
    [MDateJoined] DateTime,
    PRIMARY KEY ([MemberID]), 
    FOREIGN KEY ([ProgID]) REFERENCES [Program]
);

CREATE TABLE [MemberCard] 
(
    [MemberCardID] int IDENTITY(1,1),
    [MemberID] int,
    [MemberDetailsID] int,
    [MActiveORInactive] varchar (1),
    [Photo] image,
    [LastLogin] DateTime,
    [LoginFreq] Varchar,
    [PaymentUTD] varchar,
    [IssueDate] Date,
    [NoOfCards] int,
    PRIMARY KEY ([MemberCardID]),
    FOREIGN KEY ([MemberID]) REFERENCES [Member] ,
);

CREATE TABLE [MemberDetails] 
(
    [MemberDetailsID] int IDENTITY(1,1),
    [MemberCardID] int,
    [PaymentID] int,
    [CMAddress] Varchar (100),
    [CMCity] VarChar (50),
    [CMEmail] Varchar (100),
    [CMPhone] int ,
    PRIMARY KEY ([MemberDetailsID]),
    FOREIGN KEY ([MemberCardID]) REFERENCES [MemberCard]
)

CREATE TABLE [EmergencyContact] 
(
    [CECID] Int IDENTITY(1,1),
    [MemberHistoryID] Int,
    [CECFirstName] VarChar(60),
    [CECLastName] Varchar(60),
    [CECPhone] Int,
    [CECAddress] Varchar ( 100 ),
    [CECDateUpdated] DateTime,
    PRIMARY KEY ([CECID]) ,
    FOREIGN KEY ([MemberHistoryID]) RERERENCES [MemberHistory]
)

我有这些较小的程序：

CREATE PROCEDURE InsertMember
    @MFirstName varchar(60),
    @MLastName varchar(60)
AS
    BEGIN TRAN
        DECLARE @MemberID int
        SET @MemberID = SCOPE_IDENTITY()

        INSERT INTO Member(MFirstName, MLastName, MDateJoined)
        VALUES (@MFirstName, @MLastName, GETDATE())
        
        IF @@ERROR <> 0 OR @@ROWCOUNT > 1
        BEGIN
            ROLLBACK TRAN
            PRINT 'ERROR Inserting Member'

            SELECT ERROR_MESSAGE() AS ErrorMessage
            
            RETURN -1
        END
        else
        begin
            Commit Tran
            print 'Member Inserted Successfully'
            return 0
        end
        go


CREATE procedure InsertMemberDetails


@CMAddress varchar(100),
@CMCity varchar (50),
@CMEmail varchar (100),
@CMPhone int

AS
begin tran
        declare @MemberDetailsID int
        set @MemberDetailsId = SCOPE_IDENTITY()
        insert into MemberDetails(CMAddress,CMCity,CMEmail,CMPhone)
        values (@CMAddress,@CMCity,@CMEmail,@CMPhone)

        if @@ERROR <> 0 or @@ROWCOUNT >1
        begin
                ROLLBACK TRAN
                print 'ERROR Inserting MemberDetails!!!'
                select ERROR_MESSAGE() AS ErrorMessage
            
                return -1
        end
        else
        begin
            Commit Tran
            print 'MemberDetails Inserted Successfully'
            return 0
        end
        go

CREATE procedure InsertEmergencyContact


@CECFirstName varchar(60),
@CECLastName varchar (60),
@CECPhone int ,
@CECAddress varchar (100)



AS
begin tran
        declare @CECID int
        set @CECID = SCOPE_IDENTITY()
        insert into EmergencyContact(CECFirstName,CECLastName,CECPhone,CECAddress,CECDateUpdated)
        values (@CECFirstName,@CECLastName,@CECPhone,@CECAddress,Getdate())

        if @@ERROR <> 0 or @@ROWCOUNT >1
        begin
                ROLLBACK TRAN
                print 'ERROR Inserting Emergency Contact Details!!!'
                select ERROR_MESSAGE() AS ErrorMessage
            
                return -1
        end
        else
        begin
            Commit Tran
            print 'Emergency Contact Details Inserted Successfully'
            return 0
        end
        go  

我想创建一个单独的程序来执行较小的程序，但我该怎么做呢？显然下面的一个不起作用：

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),
@CECDateUpdated date

as
begin

    insert into Member (MFirstName,MLastName,MDateJoined) 
                exec InsertMember ( @MFirstName , @MLastName , GetDate())
    declare @MemberID int
    select @MemberID = SCOPE_IDENTITY()
        exec InsertMemberDetails [@CMAddress,@CMCity,@CMPhone,@CMEmail]
        exec InsertEmergencyContact [@CECFirstName,@CECLastName,@CECPhone,@CECAddress ,@CECDateUpdated]

        end

            if @@error <> 0 or @@rowcount <> 1
                    begin
                            print 'ERROR'
                    end
                    else
                        return -1
        end

但是我怎么能调用他们的程序，所以我最终会得到一行：

exec Proc_InsertNewMember 'Michael','Goodwin','Za Cool Street','Limerick','7843273',
'[e-mail]','Rachel','Green','2121321','The other cool street','02/02/2020'

标签： sqlsql-serverstored-procedures