Tip of the day: SQL Server – In Stored Procedure if passing parameter is not null then update column value else keep old column value in Update query.

As a developer, we always try to write better code and try to reduce the possible bugs in the application. But at the same time its also important that how you write the code, how much readable it is. Well, we all are human beings and its almost impossible to write bug free code irrespective of your work experience. That’s where the process of code review comes into the picture. Importance of code review is not limited to code refactoring or bug fixing,  at-least for me it is also a solid mechanism of knowledge sharing / gathering.

So as a part of it, I was reviewing the code of stored procedure and found some unwanted if conditions. In that SP there were 3 optional parameters and those if conditions was something like this – check if parameter passed is not null then use it in update statement. And same condition for other two columns. Here is the sample code of that stored procedure.


CREATE PROCEDURE [dbo].[App_UpdateAccountInfo]
 @AcntId INT
,@CompanyID INT
,@FrnID INT = NULL
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL

AS
BEGIN

SET NOCOUNT ON;

	UPDATE [dbo].[tblAccount]
	SET
		CompanyID = @CompanyID
		,AccountNickName = @AccountNickName
		,LastUpdateOn = GETDATE()
	 WHERE AccontID = @AcntId

	 -- Check if @SystemName is not null
	 IF @SystemName IS NOT NULL
	 BEGIN

		UPDATE [dbo].[tblAccount]
		 SET
			SystemName = @SystemName
		 WHERE AccontID = @AcntId

	 END

	 -- Check if @FrnID is not null
	 IF @FrnID IS NOT NULL
	 BEGIN

		UPDATE [dbo].[tblAccount]
		SET
			FrnID = @FrnID
		 WHERE AccontID = @AcntId

	 END

END

Now what if there were more than 5 optional parameters in above SP! As a developer do you think to write 5 or more if conditions and make SP more verbose? So what should be the proper way?

Well, Let me give you one hint. What if you update column with itself in update statement. Hmm no need to think much, here is the modified code of above SP.


CREATE PROCEDURE [dbo].[App_UpdateAccountInfo]
 @AcntId INT
,@CompanyID INT
,@FrnID INT = NULL
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL

AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[tblAccount]
	SET
		CompanyID = @CompanyID
		,AccountNickName = @AccountNickName
		,SystemName = ISNULL(@SystemName, SystemName)
		,FrnID = ISNULL(@FrnID, FrnID)
		,LastUpdateOn = GETDATE()
	 WHERE AccontID = @AcntId

END

Well, SQL Server is smart enough to not write the same value on the disk which results in minimal log activity. However this is not true in some cases. There are several factors which is nicely described in Paul White’s article and this stackexchange link.

That’s it. Have you ever faced this type of scenario? write it down in the comment section. Hope you like this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s