Q. How to write Store procedure for update with optional parameters?

Note:-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