CODEWITHSUNDEEP
sqlsql-serverstored-procedurescoalesce
JDD
JDD

Reputation: 134

Update a variable number of parameters in a Stored Procedure

Assume the following please:

I have a table that has ~50 columns. I receive an import file that has information that needs to be updated in a variable number of these columns. Is there a method via a stored procedure where I can only update the columns that need to be updated and the rest retain their current value unchanged. Keep in mind I am not saying that the unaffected columns return to some default value but actually maintain the current value stored in them.

Thank you very much for any response on this.

Is COALESCE something that could be applied here as I have read in this thread: Updating a Table through a stored procedure with variable parameters

Or am I looking at a method more similar to what is being explained here: SQL Server stored procedure with optional parameters updates wrong columns

For the record my sql skills are quite weak, I am just beginning to dive into this end of the pool

JD

Upvotes: 1

Views: 1346

Answers (1)

g2server
g2server

Reputation: 5367

Yes, you can use COALESCE to do this. Basically if the parameter is passed in as NULL then it will use the original value. Below is a general pattern that you can adapt.

DECLARE @LastName NVARCHAR(50) = 'John'
DECLARE @FirstName NVARCHAR(50) = NULL;
DECLARE @ID INT = 1;

UPDATE dbo.UpdateExample
SET LastName = COALESCE(@LastName, LastName), FirstName = COALESCE(@FirstName, FirstName), 
WHERE ID = @ID

Also, have a read of this article, titled: The Impact of Non-Updating Updates

http://web.archive.org/web/20180406220621/http://sqlblog.com:80/blogs/paul_white/archive/2010/08/11/the_2D00_impact_2D00_of_2D00_update_2D00_statements_2D00_that_2D00_don_2D00_t_2D00_change_2D00_data.aspx

Basically, "SQL Server contains a number of optimisations to avoid unnecessary logging or page flushing when processing an UPDATE operation that will not result in any change to the persistent database."

Upvotes: 2

Related Questions