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
BEGIN
    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 !!!

12 thoughts on “SQL Server: Stored Procedure for update with optional parameters

  1. Hi,
    I tried a while back and implemented this solution but it backfired when I realized that this approach cannot address the situation where you want to set a field to Null and it has a value, because the isnull(@FieldParameter, FieldValue) returns The existing value.

    Just to save you some trouble if you have nullable fields

    Otherwise its a very good approach

    Thanks for listening

  2. The article is fine, what if we want to remove the already existing address in the table by using this update, if we pass null to update it wont work.(i mean we don’t want to save the address so we updated it again), Is there any solution except passing the empty string.

    • It really depends for what NULL is meant for.
      Now question is! how to pass NULL value from user interface to database if you are using textbox for address? Then you need checkbox to signalise that value from textbox is not needed. In that case, why do not pass checkbox value to stored procedure and then one can be sure that NULL value is final value for that address field. People when working with stored procedures are not considering that procedures can not work without enough parameters to complete their task, but if you look in programmers code you will find it so complex and nobody complains about it – they will tell you that it cannot be done differently. Let me tell you, it is not true!, fair share of data in code and in procedures are needed to make it work like a charm. (sorry for bad English)

  3. what if one of the columns was an about Quantity with data type int, and you need to create a stored procedure to update the table by either adding or subtracting the quantity column, how will you go about it, and if you had to use a bit number representing addition (1) and subtraction(0). I am working on a project in school and i have a similar problem for creating a stored procedure to update stock. i will appreciate your help

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