Optimize Stored Procedure

Question

I am not an extremely experienced SQL Dev. I am worried that some of the Stored Procedures I have created for my database aren't exactly optimized, and may not be all efficient when they are running.

For example, this procedure just looks like a mess to me, but I don't know what to do to clean it up.

CREATE PROCEDURE UpdatePartInfo(
    @PartID       INT,
    @Description  VARCHAR(MAX) = NULL,
    @Information  VARCHAR(MAX) = NULL,
    @Supplier     VARCHAR(100) = NULL,
    @Manufacturer VARCHAR(100) = NULL,
    @Subcategory  VARCHAR(100) = NULL
) AS
BEGIN
    IF(EXISTS(SELECT part_id FROM parts WHERE part_id = @PartID))
    BEGIN
        BEGIN TRANSACTION UpdatePart
        BEGIN TRY
            IF @Description IS NOT NULL
                BEGIN
                    UPDATE parts
                    SET part_description = @Description
                    WHERE part_id = @PartID
                END
            IF @Information IS NOT NULL
                BEGIN
                    UPDATE parts
                    SET information = @Information
                    WHERE part_id = @PartID
                END
        END TRY
    END
    ELSE
        RETURN 1
END

The rest of the procedure is the same, checking each optional parameter to see if a value was passed, and if it was, updating the value in the table for that part.

But I feel like I should be able to construct one single Update query which does everything, instead of a whole bunch of smaller ones.

Anyway, does anyone have any good resources that could help me make some better optimized code? At the very least some good places to go for learning better SQL so I can improve upon the knowledge I have at the moment?

update parts set part_description = ISNULL(@Description, part_description), information = ISNULL(@Information,information) .... where part_id = @PartID — Philᵀᴹ, Aug 10 '18 at 16:22
You have BEGIN TRANSACTION but no commit and no checking to rollback if any errors. Also, you should read - Is it a bad practice to always create a transaction?. Also you have try but no catch. — Kin Shah, Aug 10 '18 at 16:39
@sp_BlitzErik No it doesn't have to be a transaction, but I thought making it a transaction would be the best way to deal with rolling back changes in the case of an error — Skitzafreak, Aug 10 '18 at 16:39
Also, make sure you understand The Impact of Non-Updating Updates. — Erik Darling, Aug 10 '18 at 16:41
start with fundamentals and then T-SQL Querying by Itzik Ben-Gan . I have those books and they are invaluable and excellent. — Kin Shah, Aug 10 '18 at 17:07
Do you want to have the ability to explicitly set a value to NULL? This is obviously different from not passing the parameter. — Aaron Bertrand, Aug 10 '18 at 18:27
@AaronBertrand The only value in the table that is even allowed to be NULL is the information field. Every other one has to have a value. — Skitzafreak, Aug 10 '18 at 18:34
So if information currently has a value in the table, and the user wants to set it to NULL, what do they do? — Aaron Bertrand, Aug 10 '18 at 18:34
@AaronBertrand That is a good question, and I don't know how to go about solving it. — Skitzafreak, Aug 10 '18 at 18:52

Aaron Bertrand · Answer 1 · 2020-05-08T05:05:07.210

Generally, for the case where NULL can only mean that the column should not be updated, the pattern is as follows:

UPDATE dbo.table
  SET col1 = CASE WHEN @param1 IS NOT NULL THEN @param1 ELSE col1 END,
      col2 = CASE WHEN @param1 IS NOT NULL THEN @param1 ELSE col2 END,
  WHERE key = @key;

As Erik points out, there may be considerations for whether it is worth testing if the value has changed before invoking an UPDATE:

The Impact of Non-Updating Updates

In my testing, I have found that it is typically not worth checking first, but I did not take into account all of the little details Paul mentioned (I ignored LOB columns, for example):

Checking if a non-LOB column needs to be updated

And you don't need a transaction around a single UPDATE. It is by definition a transaction - it will either succeed or fail.

If you want to allow the user to override an existing value with NULL, then for those columns you could potentially pass a token value that would never appear in the data naturally, like a poop emoji. For example:

UPDATE dbo.table
  SET col1 = CASE WHEN @param1 = N'' THEN NULL
                  WHEN @param1 IS NOT NULL THEN @param1
                  ELSE @param1 END,
  ...

Or you could be more complex and literal, adding an additional parameter per nullable column, like

...
@param1 nvarchar(32) = NULL,
@ForceParam1Null bit = 0,
...
  UPDATE dbo.table
    SET col1 = CASE WHEN @param1 IS NULL 
                     AND @ForceParam1Null = 1 THEN NULL
                  WHEN @param1 IS NOT NULL THEN @param1
                  ELSE @param1 END,
    ...

Optimize Stored Procedure

1 Answers1