SQL Server: Stored Procedure for update with optional parameters
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 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.