如何使用单个存储过程 SQL Server 2008 在父表中插入单行，在子表中插入多行

Question

我有两个表：

第一个是Requisitions（父表）：

RequisitionId          int     // primary key
RequisitionNumber      varchar(20)
RequisitionBy          varchar(100)
Remarks                varchar(300)
ApprovedStatus         varchar(20)

另一个是RequisitionDetails（子表）：

RequisitionDetailId    int     // primary key
RequisitionId          int     // foreign key ("Requisitions" table)
ProductId              int
UnitConversionId       int
Quantity               decimal(18, 2)

现在，我想使用单个存储过程在Requisitions表中插入一行，并在RequisitionDetails表中插入多行。

如果事务中存在任何问题，存储过程应包含ROLLBACK命令。我需要帮助编写此存储过程。

我

创建了在"申请"表中插入一行的存储过程，并且我有插入行的申请 ID。现在使用此申请 ID 如何为申请详细信息表插入多行？

下面是存储过程。我需要扩展它：

CREATE PROCEDURE dbo.InsertRequisition
(
    @RequisitionNumber varchar(20) = NULL,
    @RequisitionBy varchar(100) = NULL,
    @Remarks varchar(300) = NULL,
    @ApprovedStatus varchar(20) = NULL
)
AS
BEGIN
SET NOCOUNT ON;
    DECLARE @requisitionId INT;
    INSERT INTO [Requisitions] 
    (
        [RequisitionNumber],
        [RequisitionBy],
        [Remarks],
        [ApprovedStatus]
    )
    VALUES
    ( 
        @RequisitionNumber,
    @RequisitionBy,
        @Remarks,
        @ApprovedStatus
    )
    SET @requisitionId = SCOPE_IDENTITY();
   END

Answer 1

如果我正确理解了这个问题，下面的代码应该能够解决你的问题。

步骤 1：在数据库中创建类型

CREATE TYPE AnyNameTableType AS TABLE
(ColumnName1 INT, ColumnName2 VARCHAR(50))

步骤 2：创建/修改存储过程以接受表类型

 CREATE PROCEDURE My_TestProc
 (@tvpVariableName AnyNameTableType READONLY)
 AS
 BEGIN
    SELECT * FROM @tvpVariableName;
   --You can use this variable as table anywhere you want
   --Select/Insert/Update from this table and update your RequisitionDetails
   --table.
 END;

步骤 3：编写 c# 代码以传递此变量。

    DataTable table = new DataTable(); //Define your datatable simillar to TableType declared in SQL
    table.Columns.Add("ColumnName1"); //Same Column Name you should use as of TableTYpe
    table.Columns.Add("ColumnName2");
    DataRow row = table.NewRow();
    row["ColumnName1"] = 1; //Fill you datatable with data you want
    row["ColumnName2"] = "Pratik";
    table.Rows.Add(row);
    table.AcceptChanges();
    SqlConnection connection = new SqlConnection("data source=yoursource;database=test;uid=sa;pwd=yourpwd");
    SqlCommand selectCommand = new SqlCommand("My_TestProc", connection);
    selectCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = selectCommand.Parameters.AddWithValue("@tvpVariableName", table);//Parameter Name
    tvpParam.SqlDbType = SqlDbType.Structured;  // Very Important Not to miss
    connection.Open(); //You can add multiple parameters also.
    grid.DataSource = selectCommand.ExecuteReader();
    grid.DataBind();
    connection.Close();

完成此步骤后，如果您有任何疑问，请告诉我。