SQL Updating Advanced PHP Parameters

Question

We want to change the way values ​​are passed from PHP to stored procedures (T-SQL). I have only a little experience with PHP, but I will try to explain this process from discussions with our web developer.

Current process

Test pattern example

To update a record, such as Field3 in this example, we will pass all existing values ​​back to the stored procedure.

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

Say to update Field3 you have to click a button. This will allow you to go to a new page that will launch the stored procedure to update the data. Since the new page does not know the values, it must execute the SELECT procedure to retrieve the values ​​before running UPDATE .

The script then redirects the user to a page that reloads the updated data, and the changes are reflected on the screen.

New process

We only need to pass the fields that we want to change.

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

Our solution is simple. First we set all updated fields to optional (so NULL can be passed). Then we check if the parameter is NULL (not passed) if it is then ignored and if it is not updated.

 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 the << 24> value has passed . The workaround for this was to assign an arbitrary value to NULL (in this case -999), so that the procedure would update NULL if an arbitrary value (-999) was passed.

This solution is rather dirty and, in my opinion, an inefficient way to solve the problem. Are there any better solutions? What are we doing wrong?

Thank you so much in advance for any answers.

Answer 1

The Valdimir method is great for passing a flag variable to determine when a value is passed or failed, and its notes about randomly choosing a value are on the right track, but I would suggest that there are some arbitrary values ​​that you will never worry about . for example -999 for an integer when you do not allow negative numbers, or '|||||||' for an empty string. Of course, this breaks some of them when you want to use negative numbers, but then you could play with numbers too large for a data type such as BIGINT, as the default parameter is -9223372036854775808 for int .... The problem really goes down to your business case about whether values ​​may or may not be resolved.

However, if you go the route this way, I would suggest 2 things. 1) do not pass the value from PHP to SQL, but instead use the default value in SQL and check if the parameter is the default value. 2) Add CHECK CONSTRAINT to the table to make sure that 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 a numerical data field that allows negative numbers, since you really have no suitable way to determine when a value should be zero or not if you cannot choose a number that will always be from the assortment. And I definitely understand how bad the BIGINT idea is for the INT example, because now your procedure will accept a number range that it should not!

Another method / slight variation of Vladimir’s proposal is to indicate when to make the field null and not update. This will get a little used to what your PHP team will not want to use, but since these flags may also be optional, they should not 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 use a stored procedure to update a table and it has fields with a zero value, I don’t think I would recommend that the parameters be optional, as this leads to business cases / situations that may be messy in the future , especially regarding numeric data types!

Answer 2

Your approach, when you use the magic number -999 for NULL , has a problem, since any approach with magic numbers has. Why is -999 ? Why not -999999 ? Are you sure -999 cannot be a normal value for a field? Even if the user is not allowed to enter -999 for this field, are you sure that this rule will remain in effect after a few years, when your application and database evolve? It is not a matter of being effective or not, but also of being right or not.

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

In any case, your current schema allows NULLs , so we must choose a different approach. Have an explicit flag for each field that tells you whether to update the field or not.

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

Set @ParamUpdateFieldN to 0 if you do not 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, the parameters of the procedure are not optional, but you use the @ParamUpdateFieldN flags to indicate which parameters contain useful values ​​and which parameters should be ignored.

Answer 3

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

and

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

Are both ways to use the same stored procedure with MsSql or Sybase. When you do not send a value, it is the same as sending a null value. If you did not set a default value in the stored procedure. In this case, the default value is used instead of the null value.

Answer 4

Not enough reputation to just comment. In my opinion, your solution is good enough, while an arbitrary value cannot be a normal value for any of the fields.

However, I would consider transmitting and storing something other than NULL (for example, "N / A"), when the field should not have an "actual" value and it will be intentionally updated by the client.