Saturday, January 3, 2009

Updating Only Non NULL parameters in Stored Procedure

All the developers who worked on SQL server surely will have encountered the following situation atleast once.
"Update only the columns with non null values in parameter in a row."
I will explain this.
Suppose you have a users table. You are using a single stored procedure to update all the columns in table.
suppose now you need to change only the field 'Name' using this stored procedure. The other parameters will be empty. Then u can use the ISNULL property to update the table.
Here is an example.
CREATE PROC [dbo].[usp_UserDetailsUpdate]
@ControlID int,
@EmailId varchar(255)=NULL,
@LoginId varchar(255)=NULL,
@Name varchar(255)=NULL
AS
UPDATE [dbo].[UserDetails]
SET
[EmailId] = ISNULL(@EmailId, EmailId),
[LoginId] = ISNULL(@LoginId,LoginId),
[Name] = ISNULL(@Name, [Name]
WHERE [ControlID] = @ControlID

No comments:

Post a Comment