title: SQL Server stored procedures for INSERT and UPDATE, better to separate or condense?
body: <p>I am creating stored procedures for inserting and updating data in my SQL Server database. At first I was creating a separate procedure for Add/Set but then I stumbled across a query that allows me to condense them into a single procedure. I wanted to check with the SO community on any possible future issues doing it this way.</p>
  
  <p><strong>Separate Procedures</strong></p>
  
  <pre><code>--INSERT Procedure
  CREATE PROCEDURE [dbo].[AddDataType]
      @TypeName [nvarchar](255),
      @TypeProperty [nvarchar](255)
  AS
  BEGIN
      SET NOCOUNT ON;
  
      INSERT INTO DataType(TypeName, TypeProperty)
      VALUES(@TypeName, @TypeProperty)
  
      SELECT SCOPE_IDENTITY()
  END
  
  --UPDATE Procedure
  CREATE PROCEDURE [dbo].[SetDataType]
      @ID [int],
      @TypeName [nvarchar](255),
      @TypeProperty [nvarchar](255)
  AS
  BEGIN
      SET NOCOUNT ON;
  
      UPDATE DataType SET TypeName = @TypeName, TypeProperty = @TypeProperty
      WHERE ID = @ID
   END
  
  EXEC AddDataType @TypeName = 'Test Name', @TypeProperty = 'Test Property' --INSERT
  EXEC SetDataType @ID = 42, @TypeName = 'Test Name', @TestProperty = 'Test Property' --UPDATE
  </code></pre>
  
  <p><strong>Combined</strong></p>
  
  <pre><code>CREATE PROCEDURE [dbo].[SetDataType]
      @ID [int] = NULL,
      @TypeName [nvarchar](255),
      @TypeProperty [nvarchar](255)
  AS
  BEGIN
      SET NOCOUNT ON;
  
      UPDATE DataType SET TypeName = @TypeName, TypeProperty = @TypeProperty
      WHERE ID = @ID
  
      IF @@ROWCOUNT = 0
          INSERT INTO DataType(TypeName, TypeProperty)
          VALUES(@TypeName, @TypeProperty)
  
      IF @ID IS NULL
          SELECT SCOPE_IDENTITY()
  END
  
  EXEC SetDataType @TypeName = 'New Type Name', @TypeProperty = 'New Type Property' --INSERT
  EXEC SetDataType @ID = 42, @TypeName = 'Updated Type Name', @TypeProperty = 'Updated Type Property' --UPDATE
  </code></pre>
  
  <p>So far I have 15 type tables that I creating procedures for and am trying to cut down on the number of procedures created, however I don't want to sacrifice performance. I know the second method is more processing, but would it be significant enough to cause issues? I don't see the type tables holding mass amounts of data, no more than 100 records with the average being around 10-20.</p>
  
  <p>Any thoughts or suggestions are appreciated.</p>