[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Creating an update Stored Procedure that does not change the fields when a the corresponding parameter is not passed in

I would like to update a record in SQL Server using a Stored Procedure. If the parameter passed in not passed in I do not want that field corisponding to the parameter updated. What is a good way to program this?

I'm hoping I don't have to write something like:
 
 ALTER PROCEDURE dbo.MyTableU
  (
    @IDParameter int,
    @parameter1 int = NULL,
    @parameter2 tinyint = NULL,
    @parameter3 smalldatetime = NULL
   )
AS
  IF (@parameter1 IS NULL AND NOT @parameter2 IS NULL AND NOT @parameter3)
   BEGIN
     UPDATE MyTable SET Value2 = @parameter2, Value3 = @parameter3
      WHERE ID = @IDParameter
   END
       
and so on.... for every combination.
0
r1goodwin
Asked:
r1goodwin
  • 2
1 Solution
 
rafranciscoCommented:
Try this:

UPDATE MyTable
SET Value2 = ISNULL(@parameter2, Value2),
       Value3 = ISNULL(@parameter3, Value3)
WHERE ID = @IDParameter
0
 
rafranciscoCommented:
I forgot the first parameter:

UPDATE MyTable
SET Value1 = ISNULL(@parameter1, Value1),
       Value2 = ISNULL(@parameter2, Value2),
       Value3 = ISNULL(@parameter3, Value3)
WHERE ID = @IDParameter
0
 
r1goodwinAuthor Commented:
Thanks! This is exactly what I was looking for.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now