title: Stored Procedure NULL Parameter
body: <p>I have a database table I am trying to do an UPDATE/INSERT to with a stored procedure.  Let's define the table like so:</p>
  
  <pre><code>CREATE TABLE Foo
  (
      Id           INT             IDENTITY(1, 1),
      Name         VARCHAR(256)    NOT NULL,
      ShortName    VARCHAR(32),
      Sort         INT
  );
  </code></pre>
  
  <p>I have written a stored procedure similar to the following:</p>
  
  <pre><code>CREATE PROCEDURE Put_Foo
  (
      @Id           INT             = NULL OUTPUT,
      @Name         VARCHAR(256),
      @ShortName    VARCHAR(32)     = NULL,
      @Sort         INT             = NULL
  )
  AS
  BEGIN
      SET NOCOUNT ON;
  
      SELECT
          @Id = F.Id
      FROM
          Foo AS F
      WHERE
          F.Name = @Name;
  
      IF (@Id IS NOT NULL)
      BEGIN
          UPDATE
              Foo
          SET
              ShortName    = @ShortName,
              Sort         = @Sort
          WHERE
              Id = @Id;
      END
      ELSE
      BEGIN
          INSERT
          INTO Foo
          (
              Name,
              ShortName,
              Sort
          )
          VALUES
          (
              @Name,
              @ShortName
              @Sort
          );
  
          SET @Id = SCOPE_IDENTITY();
      END
  
      RETURN;
  END;
  </code></pre>
  
  <p>I've greatly simplified the data structures I am dealing with but I hope this serves my point.  My question is in regards to how the parameters are processed.  Is there a way to determine within the procedure if @Sort was passed in as NULL or set NULL by the default declaration in the parameter list?</p>
  
  <p>EDIT:</p>
  
  <p>The purpose of this is that I don't want NULL parameters to override any columns in the UPDATE statement unless they are explicitly passed in that way.</p>