SQL Server 2008 – How to Update Only One Column via Stored Procedure

sql-server-2008

Here is What i Have tried.
I Have created a Table [tblUser] which allow null value and a Store procedure [spUpdateUser] with All Parameters which can be passed null except ID parameter

Whenever this procedure executes it will only update the passed (Not Null) Parameters

CREATE TABLE [dbo].[tblUser]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[FatherName] [varchar](50) NULL,
[DOB] [date] NULL
)

CREATE PROCEDURE [dbo].[spUpdateUser]
@ID int,
@UserID varchar(50) = null,
@Name varchar(50) = null,
@FatherName varchar(50) = null,
@DOB date = null
AS
BEGIN
update tblUser set
    UserID = @UserID,
    Name = @Name,
    FatherName = @FatherName,
    DOB = @DOB
    where ID = @ID
END

How can i update table [tblUser] with only passing one parameter

exec [spUpdateUser] @ID = 1, @DOB = '27 Mar 2017'

Best Answer

You can do a COALESCE between the passed in parameters and the existing values.

When the passed in value is NULL, the query will retain the existing values.

This will, however, make it difficult to update to NULL if you do actually require the Nulling of these values.

CREATE PROCEDURE [dbo].[spUpdateUser]
@ID int,
@UserID varchar(50) = null,
@Name varchar(50) = null,
@FatherName varchar(50) = null,
@DOB date = null
AS
BEGIN
update tblUser set
    UserID = COALESCE(@UserID, UserID),
    Name = COALESCE(@Name, Name),
    FatherName = COALESCE(@FatherName, FatherName),
    DOB = COALESCE(@DOB, DOB)
    where ID = @ID
END
Related Question