如何通过存储过程将DataTable与变量数据插入SQL目标表

你的思路已经很清晰了，核心就是把刚插入TBL_FAMILY_HEAD生成的family_head_id，和传入的表值类型typ_fam_mem里的每条数据结合，批量插入到tbl_family_member中。下面是完善后的存储过程实现：

完整的存储过程代码 #

CREATE PROCEDURE [dbo].[P_SET_PROFILE_REGISTRATION] 
(
    -- 家庭主表参数
    @P_NAME NVARCHAR(200),
    @P_GENDER TINYINT,
    -- 家庭成员表值类型参数
    @P_FAMILY_DT DBO.typ_fam_mem READONLY,
    -- 输出参数：1=成功，0=失败
    @V_OUT TINYINT OUTPUT
)
AS
DECLARE @FAMILY_HEAD_ID BIGINT;
BEGIN
    SET NOCOUNT ON;
    SET @V_OUT = 0; -- 默认设为失败状态

    BEGIN TRY
        -- 开启事务，确保两张表的操作原子性
        BEGIN TRANSACTION;

        -- 插入家庭主表并获取自增ID
        INSERT INTO [DBO].[TBL_FAMILY_HEAD] ([NAME], [GENDER])
        VALUES (@P_NAME, @P_GENDER);

        SET @FAMILY_HEAD_ID = SCOPE_IDENTITY();

        -- 批量插入家庭成员数据：把家庭主ID和表值类型数据关联
        IF @@ROWCOUNT > 0 AND EXISTS(SELECT 1 FROM @P_FAMILY_DT)
        BEGIN
            INSERT INTO [DBO].[tbl_family_member]
            (
                family_head_id,
                mem_name,
                mem_gender,
                mem_occupation,
                mem_maritial_status
            )
            SELECT 
                @FAMILY_HEAD_ID, -- 每个成员都关联同一个家庭主ID
                mem_name,
                mem_gender,
                mem_occupation,
                mem_maritial_status
            FROM @P_FAMILY_DT;
        END

        -- 提交事务
        COMMIT TRANSACTION;
        SET @V_OUT = 1; -- 操作成功，更新输出参数
    END TRY
    BEGIN CATCH
        -- 回滚事务，确保数据一致性
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- 可扩展：添加错误日志记录，比如写入错误表
        -- INSERT INTO error_log (error_message, error_date) VALUES (ERROR_MESSAGE(), GETDATE());
        
        SET @V_OUT = 0; -- 操作失败
    END CATCH
END

关键要点解释 #

• 事务处理：添加事务保证家庭主表和家庭成员表的操作原子性，避免出现只有家庭主数据但无成员数据的不一致情况。
• 批量插入逻辑：用INSERT...SELECT替代循环插入，一次性关联@FAMILY_HEAD_ID和表值类型的所有数据，效率更高。
• 空值判断：通过EXISTS判断表值参数是否有数据，避免无成员时执行无效插入。
• 输出参数：默认设为失败状态，成功后更新为1，方便调用方快速判断操作结果。
• 错误回滚：异常时回滚所有操作，保证数据完整性。

ASP.NET端调用提示（C#示例） #

在ASP.NET中，只需把Grid数据转换成DataTable，再作为表值参数传入存储过程：

// 构建家庭成员DataTable
DataTable familyDt = new DataTable();
familyDt.Columns.Add("mem_name", typeof(string));
familyDt.Columns.Add("mem_gender", typeof(byte));
familyDt.Columns.Add("mem_occupation", typeof(string));
familyDt.Columns.Add("mem_maritial_status", typeof(byte));

// 从GridView行填充数据
foreach (GridViewRow row in GridView1.Rows)
{
    if (row.RowType == DataControlRowType.DataRow)
    {
        DataRow dr = familyDt.NewRow();
        dr["mem_name"] = ((TextBox)row.FindControl("txtName")).Text;
        dr["mem_gender"] = byte.Parse(((DropDownList)row.FindControl("ddlGender")).SelectedValue);
        // 填充其他字段
        familyDt.Rows.Add(dr);
    }
}

// 调用存储过程
using (SqlConnection conn = new SqlConnection(yourConnectionString))
{
    SqlCommand cmd = new SqlCommand("P_SET_PROFILE_REGISTRATION", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    
    // 添加家庭主表参数
    cmd.Parameters.AddWithValue("@P_NAME", txtHeadName.Text);
    cmd.Parameters.AddWithValue("@P_GENDER", byte.Parse(ddlHeadGender.SelectedValue));
    
    // 添加表值参数
    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@P_FAMILY_DT", familyDt);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.typ_fam_mem";
    
    // 输出参数
    SqlParameter outParam = cmd.Parameters.Add("@V_OUT", SqlDbType.TinyInt);
    outParam.Direction = ParameterDirection.Output;
    
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    
    // 处理结果
    byte result = (byte)outParam.Value;
    if (result == 1)
    {
        // 操作成功逻辑
        Response.Write("注册成功！");
    }
    else
    {
        // 操作失败逻辑
        Response.Write("注册失败，请重试！");
    }
}

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