Sql-server – Optimize Stored Procedure

optimizationsql server

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?

Best Answer

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:

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):

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,
    ...
Related Question