c# - 使用 SQL Server 中的存储过程动态更新

Question

我们有如下表格:

CREATE TABLE dbo.Test (
    StartDate DATETIME2 NOT NULL,
    EndDate DATETIME2 NULL
    Field1 NVARCHAR(50) NOT NULL,
    Field2 NVARCHAR(50) NOT NULL,
    etc.
)

如果我们插入一行，则 EndDate 始终为 NULL。如果涉及更新，我们不会真正更新现有行，但会设置它的 EndDate 并插入一个新行。假设有一个用于插入和更新的存储过程。

DECLARE @newStartDate DATETIME2
SELECT @newStartDate = GETDATE()

UPDATE dbo.Test SET (EndDate = @newStartDate) WHERE StartDate = @someDate

INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2) 
     SELECT @newStartDate, NULL, Field1, Field2 
       FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate

我们可能需要设置与旧值不同的其他开始/插入值。这可以按如下方式完成。

INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2) 
    SELECT @newStartDate, NULL, @parameter, Field2 
      FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate

现在的主要问题是真实的表包含超过 100 列，上面的方法可以工作但是很容易出错。

我正在寻找一种动态方法，其中插入语句动态获取参数值(如果存在)。

在 C# 中，我会做这样的事情。 existing 将是一个包含每一列的 Dictionary，overrides 是需要更改的非默认值。

var existing= ..."SELECT TOP 1 * from dbo.Test"
foreach (var changeParameter in overrides)
     existing[changeParameter.Key] = changeParameter.Value;

在 existing 中现在存储了所有值，因为它们应该用于插入。因此我可以构建一个新的插入语句。

这种方法在数据库上是否可行？我不想加载所有数据行只是为了立即将其写回。我宁愿直接在数据库上做所有事情。

Answer 1

I'm looking for a dynamic approach where the insert statement dynamically takes the parameter value if it exists.

这是我一直这样做的方式:

通过给它一个默认值 NULL 使参数可选。

然后 COALESCE 如果未提供参数值，则参数与您要使用的字段:

INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2) 
    SELECT @newStartDate, NULL, COALESCE(@parameter,Field1), Field2 
      FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate

就这么简单。

编辑:

如果您使用表值参数，并加入它，那么您的 COALESCE 看起来像这样，如果您在 TVP 中每个字段有 1 列:

COALESCE(tvp.Field1, Test.Field1)

或者，如果您将 TVP 作为 FieldName、FieldValue 对的垂直表传递，那么您将不得不使用 CASE 表达式或子查询，如下所示:

COALESCE((SELECT FieldValue FROM TVP WHERE FieldName='Field1'),Field1)