SQL 更新可选参数 PHP

我们想要改变将值从 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 if a NULL value is通过了。解决这个问题的方法是将任意值分配给 equalNULL（在本例中为-999），以便程序将更新NULL如果传递任意值（-999）。

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

非常感谢您的回复

就传递标志变量来识别值何时传递或不传递而言，Valdimir 的方法很棒，而且他关于任意选择值的注释是正确的，但我猜想有一些任意值你可能永远不必担心。例如，当不允许负数时，整数为 -999，或“|||||||”对于空字符串。当然，当您确实想使用负数时，这会破坏一些功能，但是您可能会使用对于数据类型来说太大的数字，例如 BIGINT 作为 int 的参数默认值 -9223372036854775808 ....问题确实如此根据您的业务案例确定值是否可以被允许。

然而，如果你走这样的路线，我会建议两件事。 1) 不要将值从 PHP 传递到 SQL，而是将其设置为 SQL 中的默认值，并测试参数是否为默认值。 2）向表添加CHECK CONSTRAINT以确保值不被使用并且不能在表中表示

所以像这样：

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 for INT 示例是多么糟糕的想法，因为现在您的程序将接受不应该接受的数字范围！

弗拉基米尔建议的另一种方法/略有变化是标记何时使字段为空而不是何时更新。您的 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

基本上，如果您使用存储过程来更新表并且它具有可为空的字段，我认为我不建议将参数设置为可选，因为它会导致将来可能会变得混乱的业务案例/情况，特别是对于数字数据类型！