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

Posted on 2005-04-20
Last Modified: 2010-03-19
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:
    @IDParameter int,
    @parameter1 int = NULL,
    @parameter2 tinyint = NULL,
    @parameter3 smalldatetime = NULL
  IF (@parameter1 IS NULL AND NOT @parameter2 IS NULL AND NOT @parameter3)
     UPDATE MyTable SET Value2 = @parameter2, Value3 = @parameter3
      WHERE ID = @IDParameter
and so on.... for every combination.
Question by:r1goodwin
    LVL 28

    Accepted Solution

    Try this:

    UPDATE MyTable
    SET Value2 = ISNULL(@parameter2, Value2),
           Value3 = ISNULL(@parameter3, Value3)
    WHERE ID = @IDParameter
    LVL 28

    Expert Comment

    I forgot the first parameter:

    UPDATE MyTable
    SET Value1 = ISNULL(@parameter1, Value1),
           Value2 = ISNULL(@parameter2, Value2),
           Value3 = ISNULL(@parameter3, Value3)
    WHERE ID = @IDParameter

    Author Comment

    Thanks! This is exactly what I was looking for.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now