0

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'
2
  • Does Name, FhaterName and DOB allow nulls in your table schema?
    – McNets
    Mar 27 '18 at 13:16
  • Yes But I Want Other Column value to be Untouched on Update Query Mar 27 '18 at 13:21
0

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
0
0
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
GO
INSERT INTO dbo.tblUser VALUES (1, 'John', 'Father John', '20180101');
GO
1 rows affected
EXEC dbo.spUpdateUser @ID=1, @FatherName = 'My Father';
GO
1 rows affected
SELECT * FROM tblUser;
GO
ID | UserID | Name | FatherName | DOB                
-: | :----- | :--- | :--------- | :------------------
 1 | 1      | John | My Father  | 01/01/2018 00:00:00
EXEC dbo.spUpdateUser @ID=1, @DOB = '20180105';
GO
1 rows affected
SELECT * FROM tblUser;
GO
ID | UserID | Name | FatherName | DOB                
-: | :----- | :--- | :--------- | :------------------
 1 | 1      | John | My Father  | 05/01/2018 00:00:00

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.