更新存储过程中的不同列

I'm trying to port over a concept that I use in Java code that I've used to SQL stored procedures and I'm not sure if it's possible.

我正在尝试移植我在Java代码中使用的概念,我已经习惯了SQL存储过程,我不确定它是否可行。

Essentially, if I have a table with 10 columns I want to be able to update different combinations of columns from the same stored procedure each time. I.e. in Java I would pass a map containing the values that I want to update and iterate through them and update each key value in the cache.

基本上,如果我有一个包含10列的表,我希望每次都能够从同一存储过程更新不同的列组合。即在Java中,我将传递一个包含我想要更新的值的映射,并迭代它们并更新缓存中的每个键值。

This has the benefit of not having to change the method signature to include each column name and not having to have a large piece of code doing a null check on each variable to figure out if it should be updated or not. I'm not sure if this is possible or whether certain database servers are capable of it (i.e. PostgreSQL, Oracle, MySQL) or they're all capable of it but I just can't figure out the syntax.

这样做的好处是不必更改方法签名以包含每个列名,也不必让大块代码对每个变量进行空检查,以确定是否应该更新它。我不确定这是否可行,或者某些数据库服务器是否具备它(即PostgreSQL,Oracle,MySQL),或者他们都能够做到这一点,但我无法弄清楚语法。

2 个解决方案

#1

In SQL Server, the below procedure would work assuming a table like this:

在SQL Server中,以下过程可以使用如下表格:

CREATE TABLE MyBigTable(
    ID INT NOT NULL PRIMARY KEY,
    COLUMNA VARCHAR(100) NOT NULL,
    COLUMNB VARCHAR(100) NOT NULL
 )

GO

CREATE PROCEDURE UpdateMyBigTable(@ID INT, @ColumnAValue VARCHAR(100)=NULL, @ColumnBValue VARCHAR(100)=NULL)
AS BEGIN
UPDATE MyBigTable
    SET COLUMNA = CASE WHEN @ColumnAValue IS NULL THEN COLUMNA ELSE @ColumnAValue END,
    COLUMNB = CASE WHEN @ColumnBValue IS NULL THEN COLUMNB ELSE @ColumnBValue END
WHERE ID = @ID
    AND ((COLUMNA <> @ColumnAValue AND @ColumnAValue IS NOT NULL) OR (COLUMNB <> @ColumnBValue AND @ColumnBValue IS NOT NULL));
END

GO

However, this doesn't actually enumerate through the fields which is what you were asking about. That is difficult to do in SQL Server without getting into dynamic SQL.

但是,这实际上并没有通过您所询问的字段进行枚举。在没有进入动态SQL的情况下,在SQL Server中很难做到这一点。

#2

For best performance either use dynamic SQL or an IF statement with a SQL statement for each possible combination of query parameters.

为获得最佳性能,请为每个可能的查询参数组合使用动态SQL或带有SQL语句的IF语句。

If you can stomach making the IF statements for each possible solution, go with that - it's stable, secure and performant.

如果您可以为每个可能的解决方案制作IF语句,那么请使用它 - 它稳定,安全且高效。

If you have too many parameters for this, use dynamic SQL - you can generate your dynamic SQL in the stored procedure or earlier, just be sure to avoid SQL injection issues.

如果您有太多参数,请使用动态SQL - 您可以在存储过程或更早版本中生成动态SQL,只需确保避免SQL注入问题。

Your problem is very similar to the dynamic search problem covered by Erland Sommarskog.

您的问题与Erland Sommarskog所涵盖的动态搜索问题非常相似。

Performance-wise, SQL Server will create an execution plan for every possible dynamic query type so performance will be similarly as good as having an IF statement to select each possible SQL statement.

在性能方面,SQL Server将为每种可能的动态查询类型创建执行计划,因此性能与使用IF语句选择每个可能的SQL语句一样好。

This is in stark contrast to using COLUMNB = CASE WHEN @COLUMNB IS NULL THEN COLUMNB ELSE @COLUMNB END which will have only one execution plan and thus frequently a poor execution plan (depending on your where clause and indexes in the DB) and poor performance.

这与使用COLUMNB = CASE WHEN COLUMNB ELSE @COLUMNB END只有一个执行计划,因此经常是一个糟糕的执行计划(取决于您的where子句和DB中的索引)和性能不佳形成鲜明对比。

#1