Best practice: Update using several optional param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best practice: Update using several optional param

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2009-03-02 : 05:25:22

Dear all,
I have a SP which is working fine but I'd like to have your advises regarding best practices.

My SP is called 'UpdateRecord'. A record contains about 20 fields, like Status, User, Start Date, Due Date etc. UpdateRecord-SP has 1 mandatory field (RowID) and all other fields are optional so the SP might be called just for changing the status, or maybe for updating ALL fields.

Currently my SP checks for all fields whether they are supplied or not. If supplied, some checks will be done whether they are valid. IF not supplied, the existing value is retrieved. Later on ALL fields are updated, regardless whether they were supplied or retrieved from existing record.

I'm not sure whether this is best practice. I had some other ideas but they all have their disadvantages:

1. Splitting SP into 20 different SPs (UpdateStartDate, UpdateDueDate, UpdateStatus etc.) will only update the modified fields, but I guess it's slower because the SP then returns to my application which then sends the next SP etc. and this might be very time-consuming.
2. Checking for each parameter whether it's modified. If so, do an update immediately, not at the end. This way only the modified fields are updated, but the single SP still might do up to 20 UPDATES which might be more time-consuming than using just one single UPDATE.
3. Keep it as it is.

What is your opinion / advise? Many thanks!

Here is a code example:


PROCEDURE [dbo].[UpdateRecord]
	@RowID INT, 
	@StartDate DATETIME = NULL, 
	@DueDate DATETIME = NULL, 
	@Priority VARCHAR(20) = NULL,
	@Status VARCHAR(20) = NULL,
	@Subject VARCHAR(150) = NULL, 
....

IF @StartDate IS NULL AND @DueDate IS NULL
BEGIN
	SELECT @StartDate = StartDate, @DueDate = DueDate From Records WHERE RowID = @RowID
END
ELSE
BEGIN
	-- @StartDate is not empty
	IF @StartDate IS NULL
		SELECT @StartDate = StartDate From Records WHERE RowID = @RowID
	ELSE
	BEGIN
	IF ISDATE(@StartDate) <> 1 
		SET @ErrorMessage = @ErrorMessage + 'Invalid Startdate (' + @StartDate + '). '
	END
	-- @DueDate is not empty
	IF @DueDate IS NULL
		SELECT @DueDate = DueDate From Records WHERE RowID = @RowID
	ELSE
	BEGIN
	IF ISDATE(@DueDate) <> 1
		SET @ErrorMessage = @ErrorMessage + 'Invalid DueDate (' + @DueDate + '). '
	END
	IF ISDATE(@StartDate) = 1 AND ISDATE(@DueDate) = 1
	BEGIN
	IF @StartDate > @DueDate 
		SET @ErrorMessage = @ErrorMessage + 'Start Date is after Due Date. '
	END
END

IF @Subject IS NULL
	SELECT @Subject = Subject From Records WHERE RowID = @RowID

....

UPDATE Records
	SET StartDate = @StartDate, DueDate = @DueDate,
	Subject = @Subject,
	...
WHERE RowID = @RowID

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-03-02 : 05:43:13

I have one suggestion, keep all the validations as it is, remove the retrieving part.
Use [CODE]UPDATE Records
SET StartDate = ISNULL(@StartDate, StartDate),
DueDate = ISNULL(@DueDate, DueDate ),
Subject = ISNULL(@Subject, Subject ),
...
WHERE RowID = @RowID
[/CODE]

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2009-03-02 : 07:49:41

Hi PeterNeo,

goid point, thanks! I've added that to my SP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources