SQL Server: Stored Procedure for update with optional parameters

Problem:

One more article in Problem-Solution approach to keep it short and simple.

When we are writing a stored procedure to update different fields of a table for one or more records, we may write as the below script.

CREATE PROCEDURE [dbo].[UpdtEmployee]
(
@EmpId INT,
@EmpName VARCHAR(100)=NULL,
@Address VARCHAR(200)=NULL,
@EmpPhotoPath VARCHAR(100)=NULL,
@MobileNo VARCHAR(14)=NULL
)
AS
BEGIN
UPDATE dbo.Employee
SET EmpName=@EmpName,Address=@Address,EmpPhotoPath=@EmpPhotoPath,MobileNo=@MobileNo
WHERE EmpId=@EmpId
END

The problem with the above stored procedure is when we want to update only those fields/columns of the record for which corresponding parameter is not having NULL values then it will not yield the anticipated result. In this scenario this stored procedure will update all the fields with the values from corresponding parameters. Let’s take an example so that the problem can be understood clearly.

Example: Let’s assume that we have inserted values to all the fields of a record. Now we want to modify only the mobile number of that employee. If we call this procedure then it will make values of all other fields to NULL as we have declared the default value for all the parameters as NULL.

Solution:

Solution to this problem is to avoid updating those fields which have corresponding parameters with NULL values or, we can update existing data corresponding to those fields. We will go for the later one. Now, the modified script may look like

CREATE PROCEDURE [dbo].[UpdtEmployee]
(
 @EmpId INT,
 @EmpName VARCHAR(100)=NULL,
 @Address VARCHAR(200)=NULL,
 @EmpPhotoPath VARCHAR(100)=NULL,
 @MobileNo VARCHAR(14)=NULL
)
AS
BEGINd
 UPDATE dbo.Employee
 SET EmpName=ISNULL(@EmpName,EmpName),Address=ISNULL(@Address,Address),EmpPhotoPath=ISNULL(@EmpPhotoPath,EmpPhotoPath),MobileNo=ISNULL(@MobileNo,MobileNo)
 WHERE EmpId=@EmpId
END

Hopefully this article may be useful to you.

Thanks !!!

About these ads