title: T-SQL developer insert & merge stored procedure able to modify to NULL
body: <p>Is there a better SQL Server 2008 R2 technique to code the following combined <code>INSERT</code> &amp; <code>UPDATE</code> procedure that allows NULL <code>INSERT</code>?</p>
  
  <p>I am very interested in seeing how other developers write <code>INSERT</code> &amp; <code>UPDATE</code> procedures that can handle NULL inserts (imagine a user wanting to undo an entry). I appreciate there will be more sophisticated and elegant solutions using <code>MERGE</code> or some transaction rollback technique which I am interested in seeing, however, I do ask that you build up your example from first principles as this may result in the post having a wider appeal no matter what the readers T-SQL level.</p>
  
  <p>The basis of this simplistic example is an <code>Orders</code> table tracking stock purchases. The procedure should only allow <code>UPDATES</code> when the <code>OrderStatus</code> is the same or increasing..</p>
  
  <pre><code>OrderStatus  Explanation
  -------------------------
       0       Creation
       1       Checking
       2       Placement
       3       Execution
       ...
       8       Settlement
  </code></pre>
  
  <p>Table structure:</p>
  
  <pre><code>CREATE TABLE Orders(
      OrderID INT IDENTITY,
      Ticker VARCHAR(20) NOT NULL,
      Size DECIMAL(31,15) NULL,
      Price DECIMAL(31,15) NULL,
      OrderStatus TINYINT NOT NULL)
  </code></pre>
  
  <p>Further, let's imagine we have the following data so that we can test modifying the data:</p>
  
  <pre><code>SET IDENTITY_INSERT [dbo].[Orders] ON
  INSERT INTO [dbo].[Orders] ([OrderID], [Ticker], [Size], [Price], [OrderStatus]) 
  VALUES  (1, N'MSFT', CAST(1 AS Decimal(31, 15)), NULL, 0)
          ,(2, N'GOOG', CAST(2 AS Decimal(31, 15)), CAST(523 AS Decimal(31, 15)), 5)
          ,(3, N'AAPL', CAST(1 AS Decimal(31, 15)), NULL, 0)
  SET IDENTITY_INSERT [dbo].[Orders] OFF
  </code></pre>
  
  <p>You should have the following data:</p>
  
  <pre><code>OrderID Ticker  Size    Price   OrderStatus
  -----------------------------------------------
  1       MSFT    1.000   NULL    0
  2       GOOG    2.000   523.000 5
  3       AAPL    1.000   NULL    0
  </code></pre>
  
  <p>Now for the interesting part. This is my best effort to design a combined <code>INSERT</code> &amp; <code>UPDATE</code> procedure that can deal with NULL inserts (i.e. allow the user to undo an entry). Notice that I need an input parameter to differentiate whether the input value of NULL is intentional and needs to be written into the table vs. the NULL that appears as a missing input parameter. Hopefully it's very clear why I am asking this question as I find my technique very verbose.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Upsert_Orders] @isNullInsert BIT = 0
      ,@OrderID INT = NULL
      ,@Ticker VARCHAR(20) = NULL
      ,@Size VARCHAR(100) = NULL
      ,@Price VARCHAR(100) = NULL
      ,@OrderStatus TINYINT = NULL
  AS
  BEGIN
      IF (@OrderID IS NOT NULL)
          -- First check if @OrderID exists
          IF (
                  SELECT OrderID
                  FROM dbo.Orders
                  WHERE OrderID = @OrderID
                  ) IS NULL
          BEGIN
              -- @OrderID does not exist therefore replace with NULL
              SET @OrderID = NULL
              PRINT 'spUO. Replaced OrderID ' + CAST(@OrderID AS VARCHAR) + ' input parameter with NULL.'
          END
  
      IF @OrderID IS NULL
      BEGIN
          -- @OrderID IS NULL so INSERT a new record
          PRINT 'spUO Inserting a new record into the Orders'
  
          INSERT INTO Orders (
              -- OrderID not needed as IDENTITY new record.
              Ticker
              ,Size
              ,Price
              ,OrderStatus
              )
          VALUES (
              -- @OrderID not needed as IDENTITY new record
              @Ticker
              ,@Size
              ,@Price
              ,@OrderStatus
              )
      END
      ELSE
      BEGIN
          -- @OrderID IS NOT NULL therefore UPDATE the record @OrderID
          PRINT 'spUO Modifying existing record with OrderID ' + CAST(@OrderID AS VARCHAR)
  
          -- Declare CurrentVariables for @OrderID
          DECLARE -- @CurrentOrderID INT not needed as @OrderID Found
              @CurrentTicker VARCHAR(20)
              ,@CurrentSize DECIMAL(31, 15)
              ,@CurrentPrice DECIMAL(31, 15)
              ,@CurrentOrderStatus TINYINT
  
          -- Populate Current Variables from Table Orders
          SELECT -- @CurrentOrderID = OrderID not needed as @OrderID Found
              @CurrentTicker = Ticker
              ,@CurrentSize = Size
              ,@CurrentPrice = Price
              ,@CurrentOrderStatus = OrderStatus
          FROM Orders
          WHERE OrderID = @OrderID
  
          IF ISNULL(@OrderStatus, @CurrentOrderStatus) &gt;= @CurrentOrderStatus
          BEGIN
              -- Update @OrderID if not moving backwards
              IF @isNullInsert = 0
              BEGIN
                  -- We are not updating the record with NULL
                  PRINT 'spUO NULL Parameter Input Values get replaced with the existing entries'
  
                  UPDATE Orders
                  SET -- OrderID = ISNULL(@OrderID, @CurrentOrderID)  not needed as @OrderID Found
                      Ticker = ISNULL(@Ticker, @CurrentTicker)
                      ,Size = ISNULL(@Size, @CurrentSize)
                      ,Price = ISNULL(@Price, @CurrentPrice)
                      ,OrderStatus = ISNULL(@OrderStatus, @CurrentOrderStatus)
                  WHERE OrderID = @OrderID
              END
              ELSE
              BEGIN
                  -- We are potentially overwritting the record with NULL
                  PRINT 'spUO Old entries may be overwritten with NULL'
  
                  UPDATE Orders
                  SET -- OrderID = ISNULL(@OrderID, @CurrentOrderID)  not needed as @OrderID Found
                      Ticker = @Ticker
                      ,Size = @Size
                      ,Price = @Price
                      ,OrderStatus = @OrderStatus
                  WHERE OrderID = @OrderID
              END
          END
          ELSE
              -- User is trying to re-write hostory. Do Nothing
              PRINT 'spUO You do not have permissions to roll back the OrderStatus.' 
      END
  END
  </code></pre>
  
  <p>Now that we have an UPSERT procedure, let me illustrate its usage:</p>
  
  <p>Step 1: insert a new row to show the intention to buy some Ford shares:</p>
  
  <pre><code>EXEC dbo.Upsert_Orders  @Ticker = 'F',
                          @Size = 1,
                          @Price = 10,
                          @OrderStatus = 2
  </code></pre>
  
  <p>Step 2: let's show that the <code>OrderStatus</code> can't be wound back</p>
  
  <pre><code>EXEC dbo.Upsert_Orders  @OrderID = 4,
                          @Ticker = 'F',
                          @Size = 1,
                          @Price = 10,
                          @OrderStatus = 1
  </code></pre>
  
  <p>This produces the desired output:</p>
  
  <pre><code>spUO Modifying existing record with OrderID 4 
  spUO You do not have permissions to roll back the OrderStatus.
  </code></pre>
  
  <p>The data now looks like:</p>
  
  <pre><code>OrderID Ticker  Size    Price   OrderStatus
  -----------------------------------------------
  1       MSFT    1.000   NULL    0
  2       GOOG    2.000   523.000 5
  3       AAPL    1.000   NULL    0
  4       F       1.000   10.000  2
  </code></pre>
  
  <p>Step 3: finally, let's assume the user wants to delete the shares of the first order, then the unfortunate method under my procedure requires the other default parameters be passed and the <code>@isNULLInsert BIT</code> needs to be set to 1.</p>
  
  <pre><code>EXEC dbo.Upsert_Orders  @isNullInsert = 1,
                          @OrderID = 1,
                          @Ticker = 'MSFT',
                          @Size = NULL,
                          @Price = NULL,
                          @OrderStatus = 0
  </code></pre>
  
  <p>Hopefully this complete example illustrates the concept in adding a new record, updating an existing record and deleting a field of a record. Apologies for the length of this post, but this is the most concise code I was able to produce! </p>
  
  <p>Final data:</p>
  
  <pre><code>OrderID Ticker  Size    Price   OrderStatus
  ------------------------------------------------
  1       MSFT    NULL    NULL    0
  2       GOOG    2.000   523.000 5
  3       AAPL    1.000   NULL    0
  4       F       1.000   10.000  2
  </code></pre>
  
  <p>Thanks All,</p>
  
  <p>Bertie.</p>
  
  <p>p.s. This will be called from Excel VBA.</p>