Is There A More Efficient Way Of Doing This?

Question

Here is my code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE UpdateMyTable
  @Id int,
  @Field1 int = NULL,
  @Field2 int = NULL,
  @Field3 int = NULL,
  @Field4 datetime = NULL,
  @Field5 datetime = NULL,
  @Field6 int = NULL
AS
BEGIN
  SET NOCOUNT ON;

  IF @Field1 IS NULL
    SET @Field1 = (SELECT Field1
            FROM MyTable
            WHERE Id = @Id)
              
  IF @Field2 IS NULL
    SET @Field2 = (SELECT Field2
            FROM MyTable
            WHERE Id = @Id)
            
  IF @Field3 IS NULL
    SET @Field3 = (SELECT Field3
            FROM MyTable
            WHERE Id = @Id)
             
  IF @Field4 IS NULL
    SET @Field4 = (SELECT Field4
            FROM MyTable
            WHERE Id = @Id)
             
  IF @Field5 IS NULL
    SET @Field5 = (SELECT Field5
            FROM MyTable
            WHERE Id = @Id)
            
  IF @Field6 IS NULL
    SET @Field6 = (SELECT Field6
            FROM MyTable
            WHERE Id = @Id)
  UPDATE
    MyTable 
  SET
    Field1 = @Field1,
    Field2 = @Field2,
    Field3 = @Field3,
    Field4 = @Field4,
    Field5 = @Field5,
    Field6 = @Field6
  WHERE
    Id = @Id

END
GO

I'm wondering if there is a better way to write the above. Basically, the Update stored procedure should take everything as optional. Wherever the caller specifies a value it should be used, wherever the caller does not, it should be populated with the current value.

Answer 1

Try it this way:

UPDATE MyTable
SET Field1=ISNULL(@Field1,Field1)
     ,Field2=ISNULL(@Field2,Field2)
     ...
WHERE Id = @ID

 

---

--Brad (My Blog)

Answer 2

That's perfect, thanks!

Answer 3

Basically, the Update stored procedure should take everything as optional. Wherever the caller specifies a value it should be used, wherever the caller does not, it should be populated with the current value.

CREATE PROCEDURE UpdateFoobar
 @in_magical_generic_id INTEGER,
 @in_field_1 INTEGER = NULL,
 @in_field_2 INTEGER = NULL,
 @in_field_3 INTEGER = NULL,
 @in_field_4 DATE = NULL,
 @in_field_5 DATE = NULL,
 @in_field_6 INTEGER = NULL
AS
UPDATE Foobar
   SET field_1 = COALESCE (@in_field_1, field_1)
       field_2 = COALESCE (@in_field_2, field_2),
       field_3 = COALESCE (@in_field_3, field_3),
       field_4 = COALESCE (@in_field_4, field_4),
       field_5 = COALESCE (@in_field_5, field_5),
       field_6 = COALESCE (@in_field_6, field_6)
 WHERE magical_generic_id
       = @in_magical_generic_id;

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL