SQL更新可选参数PHP

Question

我们希望改变从PHP传递值到存储过程（T-SQL）的方式。我只对PHP有一些基础经验，但我会尝试根据与我们的Web开发人员的讨论来解释这个过程。 当前流程 示例测试表格

为了更新记录，比如此例中的Field3，我们需要将所有现有的值传回存储过程。

EXEC dbo.UpdateTest @ID = 1, @Field1 = 'ABC', @Field2 = 'DEF', @Field3 = 'GHI', @Field4 = 'JKL'

假设要更新Field3，您必须单击一个按钮。这将导航到一个新页面，该页面将运行存储过程以更新数据。由于新页面不知道它必须运行的值，因此必须运行SELECT过程以检索值，然后运行UPDATE。

然后脚本会将用户重定向回重新加载更新数据并在屏幕上反映更改的页面。

新流程

我们想要做的是仅传递要更改的字段。

EXEC dbo.UpdateTest @ID = 1, @Field2 = 'DEF', @Field3 = 'GHI'

我们的解决方案很简单。首先，将所有可更新字段设置为可选的（因此可以传递 NULL）。然后，我们检查参数是否为 NULL（未传递），如果是，则忽略它，如果不是，则进行更新。

UPDATE 
    dbo.Test
SET
    Field1 = NULLIF(ISNULL(@Field1,Field1),'-999')
    ,Field2 = NULLIF(ISNULL(@Field2,Field2),'-999')
    ,Field3 = NULLIF(ISNULL(@Field3,Field3),'-999')
    ,Field4 = NULLIF(ISNULL(@Field4,Field4),'-999')
WHERE
    ID = @ID

然而，我们仍希望在传递NULL值时将数据库记录更新为NULL。解决此问题的方法是将任意值赋值为等于NULL（在本例中为-999），以便当传递该任意值（-999）时，过程将更新NULL。

这种解决方案相当混乱，并且在我看来是解决问题的一种低效方式。有更好的解决方案吗？我们做错了什么吗？

非常感谢您提前的回复。

- Zakerias

---

不确定我是否理解了问题。但是，由于您在执行存储过程时传递了命名参数。 EXEC dbo.UpdateTest ID = 1, Field2 = 'DEF', Field3 = 'GHI' 您正在将 null 值发送到所有其他字段。您不需要做任何事情，该值将为 null。我不会传递-999的值，因为我看不到任何需要它的地方。您在数据库中写入什么，null 还是 -999？ - M T Head

"what we would like to pass"这一行没有问题。如果你正在使用的是MsSql服务器，不同的Sql平台工作方式也可能不同。 - M T Head

我在答案部分发布了这篇文章，因为它使用了@符号，如果我把它们放在评论部分，它会尝试阻止我。 - M T Head

你能否将参数用XML进行包装并传递给程序？SQL Server可以解析XML，因此它允许传递和解析任何组合的参数。或许这很有趣？请参考：https://dev59.com/qW_Xa4cB1Zd3GeqPwQbP ， http://www.itworld.com/article/2960645/development/tsql-how-to-use-xml-parameters-in-stored-procedures.html 和 https://www.simple-talk.com/blogs/using-xml-to-pass-lists-as-parameters-in-sql-server/。 - Ryan Vincent

Answer 1

瓦尔迪米尔的方法非常好，可以传递一个标志变量来识别是否传递了值，他关于任意选择值的注释也是正确的，但我想你可能永远不必担心一些任意的值。例如，当您不允许负数时，整数的-999或空字符串的“|||||||”。当然，当您确实想使用负数时，这种方法有些不可行，但是您可以尝试使用超出数据类型范围的数字作为参数默认值，例如BIGINT中的-9223372036854775808。

然而，如果您采用这种方法，我建议做两件事。1）不要从PHP传递值到SQL，而是将其设置为SQL中的默认值，并测试参数是否为默认值。2）在表中添加CHECK约束以确保不使用这些值并且不能在表中表示。

因此，代码示例：

ALTER TABLE dbo.UpdateTest
CHECK CONSTRAINT chk_IsNotNullStandInValue (Field1 <> '|||||||||||||||||||' AND Field2 <> -999)

CREATE PROCEDURE dbo.UpdateTest
    @ParamId numeric(10,0)
    ,@ParamField1 NVARCHAR(250) = '|||||||||||||||||||'
    ,@ParamField2 INT = -99999  --non negative INT
    ,@ParamField3 BIGINT = -9223372036854775808 --for an int that can be negative
AS
BEGIN

DECLARE @ParamField3Value INT

BEGIN TRY

    IF ISNULL(@ParamField3,0) <> -9223372036854775808
    BEGIN
       SET @ParamField3Value = CAST(@ParamField3 AS INT)
    END
END TRY
BEGIN CATCH
    ;THROW 51000, '@ParamField3 is not in range', 1
END CATCH

    UPDATE dbo.Test
       SET Field1 = IIF(@ParamField1 = '|||||||||||||||||||',Field1,@ParamField1)
          ,Field2 = IIF(@ParamField2 = -99999,Field2,@ParamField2)
          ,Field3 = IIF(@ParamField3 = -9223372036854775808, Field3, @ParamField3Value)
    WHERE
       ID = @ParamId

END

这种方法的真正问题在于数值数据字段允许负数，因为您没有适当的方法确定何时该值应为空，除非您选择一个始终超出范围的数字。 我确实意识到 BIGINT 用于 INT 的示例是多么糟糕，因为现在您的过程将接受不应该有的数字范围！ 另一种方法/弱化 Vladimir 建议的变体是标记何时使字段为空，而不是何时更新。这需要您的 PHP 团队花费一些时间来记住使用，但因为这些标志也可以是可选的，所以它们不必总是包括类似以下内容：

CREATE PROCEDURE dbo.UpdateTest
    @ParamId numeric(10,0)
    ,@ParamField1 NVARCHAR(250) = NULL
    ,@MakeField1Null BIT = 0
    ,@ParamField2 INT = NULL
    ,@MakeField2Null BIT = 0
    ,@ParamField3 INT = NULL
    ,@MakeField3Null BIT = 0
AS
BEGIN

    UPDATE dbo.Test
       SET Field1 = IIF(ISNULL(@MakeField1Null,0) = 1,NULL,ISNULL(@ParamField1,Field1))
          ,Field2 = IIF(ISNULL(@MakeField2Null,0) = 1,NULL,ISNULL(@ParamField2,Field2))
          ,Field3 = IIF(ISNULL(@MakeField3Null,0) = 1,NULL,ISNULL(@ParamField3,Field3))
    WHERE
       ID = @ParamId

END

基本上，如果您正在使用存储过程更新表格并且它具有可空字段，则我不建议将参数设置为可选，因为这会导致未来业务情况/情境变得混乱，尤其是涉及数值数据类型！

- Matt

Answer 2

您使用一个魔数-999来代表NULL值的方法存在问题，就像任何使用魔数的方法一样。为什么是-999？为什么不是-999999？您确定-999不能成为该字段的正常值吗？即使现在禁止用户输入-999，您确定这个规则在未来几年内应用和数据库发展时仍然有效吗？这不是关于效率与否，而是关于正确性。

如果表中的字段为NOT NULL，那么可以传递NULL值来表示不应更新该字段。在这种情况下，使用魔数NULL是可以的，因为表模式保证该字段不会为NULL。未来表模式可能会更改，所以NULL可以成为字段的有效值。

无论如何，您当前的模式允许NULLs，所以我们应该选择另一种方法。为每个字段设置一个显式标志，告诉过程是否应该更新该字段。

当您想更改此字段的值时，将@ParamUpdateFieldN设置为1。过程将使用传递给相应@ParamFieldN的值。

当您不想更改此字段的值时，请将@ParamUpdateFieldN设置为0。将@ParamFieldN设置为任何值（例如NULL），则表中对应的字段将不会更改。

CREATE PROCEDURE dbo.UpdateTest
    -- Add the parameters for the stored procedure here
    @ParamID numeric(10,0),                 -- not NULL

    -- 1 means that the field should be updated
    -- 0 means that the fleld should not change
    @ParamUpdateField1 bit,                 -- not NULL
    @ParamUpdateField2 bit,                 -- not NULL
    @ParamUpdateField3 bit,                 -- not NULL
    @ParamUpdateField4 bit,                 -- not NULL

    @ParamField1 nvarchar(250),             -- can be NULL
    @ParamField2 nvarchar(250),             -- can be NULL
    @ParamField3 nvarchar(250),             -- can be NULL
    @ParamField4 nvarchar(250)              -- can be NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;
    BEGIN TRY

        UPDATE dbo.Test
        SET
             Field1 = CASE WHEN @ParamUpdateField1 = 1 THEN @ParamField1 ELSE Field1 END
            ,Field2 = CASE WHEN @ParamUpdateField2 = 1 THEN @ParamField2 ELSE Field2 END
            ,Field3 = CASE WHEN @ParamUpdateField3 = 1 THEN @ParamField3 ELSE Field3 END
            ,Field4 = CASE WHEN @ParamUpdateField4 = 1 THEN @ParamField4 ELSE Field4 END
        WHERE
            ID = @ParamID
        ;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- TODO: process the error
        ROLLBACK TRANSACTION;
    END CATCH;

END

因此，过程的参数不是可选的，但您可以使用@ParamUpdateFieldN标志来指示哪些参数包含有用的值，哪些参数应该被忽略。

- Vladimir Baranov

Answer 3

声望不足，无法仅评论。

在我看来，只要任意值不能成为任何字段的正常值，您的解决方案就足够好了。

然而，当一个字段不应该有“实际”值并且它是从客户端有意更新时，我会考虑传递和存储除NULL之外的其他内容（例如“N/A”）。

- JayValkyr

Answer 4

EXEC dbo.UpdateTest @ID = 1, @Field1 = 'ABC', @Field2 = 'DEF', @Field3 = 'GHI', @Field4 = 'JKL'

和

EXEC dbo.UpdateTest @ID = 1, @Field2 = 'DEF', @Field3 = 'GHI'

这两种方法都可以在MsSql或Sybase中使用相同的存储过程。当您不发送值时，它与发送null相同。除非您在存储过程中设置了默认值。在这种情况下，将使用默认值而不是null。

- M T Head

---

不确定我是否完全理解问题。但我认为您的更新语句将覆盖一个值，如果已经存在一个值，并且没有发送任何内容到存储过程，则该值将被覆盖为null。如果我正确理解了您的意思，我认为PHP方面是正确的，问题在于存储过程方面。 - M T Head

虽然这是正确的，但 OP 想知道处理传递 null 值的情况的最佳方法，并理解何时将该值更新为 null 或根本不更新。 - Matt

我的观点是我不认为那个 SQL 语句会做到那个效果。按照它的设置，它会接收一个 null 值并覆盖非 null 值。我认为这个问题与 PHP 没有任何关系。问题在于他们使用的 SQL 写法。如果需要的话，他们可能需要进行动态 SQL 存储过程。正在努力理解问题，想要提供帮助，但不确定他们具体想要做什么。 - M T Head