SQL 更新可选参数 PHP [英] SQL Updating Optional Parameters PHP

问题描述

我们想要改变从 PHP 传递值到存储过程 (T-SQL) 的方式.我只有很少的 PHP 经验，但我会尝试通过与我们的网络开发人员的讨论来解释这个过程.

We want to change the way we pass values from PHP to stored procedures (T-SQL). I only have minor experience with PHP but I will attempt to explain the process from discussions with our web developer.

当前流程

示例测试表

为了更新记录，例如本示例中的 Field3，我们会将所有现有值传递回存储过程.

In order to update a record, such as Field3 in this example, we would pass all existing values back to the stored procedure.

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

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

Lets say to update Field3, you must click a button. This would navigate to a new page which would run the stored procedure to update the data. As the new page is unaware of the values it has to run a SELECT procedure to retrieve the values before running an UPDATE.

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

The script would then redirect the user back to the page which reloads the updated data and the changes are reflected on screen.

新流程

我们想要做的只是传递我们想要更改的字段.

What we would like to do is only pass the fields we want to change.

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

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

Our solution is simple. First we set all of the updatable fields to optional (so NULL can be passed). We then check to see if the parameter is NULL (is not passed), if it is then we ignore it and if it isn't we update it.

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已通过.

However we still want the procedure to update the database record to NULL if a NULL value is passed. The workaround for this was to assign an arbitrary value to equal NULL (in this case -999), so that the procedure will update NULL if the arbitrary value (-999) is passed.

这个解决方案相当混乱，在我看来，这是一种低效的解决问题的方法.有没有更好的解决方案?我们做错了什么?

This solution is rather messy and, in my eyes, an inefficient way of solving the problem. Are there any better solutions? What are we doing wrong?

非常感谢您的回复

推荐答案

Valdimir 的方法非常棒，它传递了一个标志变量来标识何时传递或不传递值，并且他关于任意选择值的注释是正确的，但我猜你可能永远不必担心某些任意值.例如 -999 表示不允许负数的整数，或 '|||||||'对于空字符串.当然，当您确实想要使用负数时，这会分解一些，但是您可能会使用对于数据类型(例如 BIGINT)来说太大的数字作为参数默认值 -9223372036854775808 用于 int ......问题真的出现了是否允许或不允许值的业务案例.

Valdimir's method is great as far as passing a flag variable to identify when the value is passed or not passed and his notes about arbitrarily picking a value are right on, but I would guess that there are some arbitrary values you may never have to worry about. such as -999 for a integer when you don't allow for negative numbers, or '|||||||' for a null string. Of course this breaks down some when you do want to use negative numbers but then you could potentially play around with numbers too big for a data type such as BIGINT as a parameter default -9223372036854775808 for an int.... The issue really comes down to your business case of whether values can or can not be allowed.

但是，如果您走这样的路线，我会建议两件事.1) 不要将值从 PHP 传递给 SQL，而是将其设为 SQL 中的默认值并测试该参数是否为默认值.2)在表中添加一个CHECK CONSTRAINT，以确保这些值不被使用且不能在表中表示

However if you go a route like that, I would suggest 2 things. 1) don't pass the value from PHP to SQL instead make that the default value in SQL and test if the parameter is the default value. 2) Add a CHECK CONSTRAINT to the table to ensure the values are not used and cannot be represented in the table

比如:

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

此方法的真正问题是数字数据字段允许负数，因为您确实没有适当的方法来确定值何时应为空，除非您可以选择一个始终不在的数字范围.而且我绝对意识到用于 INT 示例的 BIGINT 的想法是多么糟糕，因为现在您的程序将接受它不应该接受的数字范围！

The real problem with this method is the numeric data field allowing for negative numbers as you really don't have an appropriate way of determining when the value should be null or not unless you can pick a number that will always be out of range. And I definitely realize how bad of an idea the BIGINT for INT example is because now your procedure will accept a numeric range that it shouldn't!

Vladimir 建议的另一种方法/略有变化是标记何时使字段为空而不是何时更新.这需要一点时间让您的 PHP 团队记住使用，但由于这些标志也可以是可选的，因此它们不必总是包含以下内容，如:

Another method/slight variation of Vladimir's suggestion is to flag when to make a field null rather than when to update. This will take a little getting used to for your PHP team to remember to use but because these flags can also be optional they don't have to be burdensome to always include something like:

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

基本上，如果您使用存储过程来更新表并且它具有可为空字段，我认为我不建议让参数是可选的，因为它会导致业务案例/情况在未来尤其是关于数字数据类型！

这篇关于SQL 更新可选参数 PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！