SQL Updating Optional Parameters PHP

Question

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.

Current Process

Example test table

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'

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.

New Process

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

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

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

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?

A huge thanks in advance to any replies

Answer 1

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.

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

So something like:

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

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!

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

Basically if you are using the stored procedure to Update a table and it has nullable fields, I don't think I would recommend having the paramaters be optional as it leads to business cases/situations that can be messy in the future especially concerning numeric data types!

Answer 2

Your approach where you use a magic number -999 for the NULL value has a problem, as any approach with magic numbers have. Why -999? Why not -999999? Are you sure that -999 can not be a normal value for the field? Even if it is not allowed for a user to enter -999 for this field now, are you sure that this rule will remain in place in few years when your application and database evolve? It is not about being efficient or not, but about being correct or not.

If your fields in the table were NOT NULL, then you could pass a NULL value to indicate that this field should not be updated. In this case it is OK to use a magic value NULL, because the table schema guarantees that the field can't be NULL. There is a chance that the table schema will change in the future, so NULL can become a valid value for a field.

Anyway, your current schema allows NULLs, so we should choose another approach. Have an explicit flag for each field that would tell the procedure whether the field should be updated or not.

Set @ParamUpdateFieldN to 1 when you want to change the value of this field. Procedure would use the value that is passed in the corresponding @ParamFieldN.

Set @ParamUpdateFieldN to 0 when you don't want to change the value of this field. Set @ParamFieldN to any value (for example, NULL) and the corresponding field in the table will not change.

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

So, parameters of the procedure are not optional, but you use @ParamUpdateFieldN flags to indicate which parameters hold useful values and which parameters should be ignored.