0

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?

  • 2
    update parts set part_description = ISNULL(@Description, part_description), information = ISNULL(@Information,information) .... where part_id = @PartID – Philᵀᴹ Aug 10 '18 at 16:22
  • Does this really need to be in a transaction? – Erik Darling Aug 10 '18 at 16:32
  • 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
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.