SQL Server: Stored Procedure for update with optional parameters

Category > MSSQLSERVER || Published on : Friday, October 30, 2015 || Views: 2676 || Stored Procedure for update with optional parameters


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