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?