Update or insert method

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 31, 2004 at 3:44 pm

  #501384

  Personally I prefer to use multipls SPs in groups with chekcing logic in first, update in another and insert in yet another so I get an execution plan for each circumstance if I am doing something liek you have.

  Ex.

  CREATE PROCEDURE dbo.usp_UpdateInsert;1

    @iPK      INT,

    @iC1      INT

  AS

  SET NOCOUNT ON

  IF EXISTS (SELECT C1 FROM dbo.Table1 WHERE  PK = @iPK)

   EXEC dbo.usp_UpdateInsert;2 @iPK, @iC1

  ELSE

   EXEC dbo.usp_UpdateInsert;3 @iPK, @iC1

  GO

  -- Does Update

  CREATE PROCEDURE dbo.usp_UpdateInsert;2

    @iPK      INT,

    @iC1      INT

  AS

  SET NOCOUNT ON

  UPDATE dbo.Table1 SET C1 = @iC1 WHERE PK = @iPK

  GO

  -- Performs Insert

  CREATE PROCEDURE dbo.usp_UpdateInsert;3

    @iPK      INT,

    @iC1      INT

  AS

  SET NOCOUNT ON

  INSERT dbo.Table1 (PK, C1) VALUES (@iPK, @iC1)

  GO

  Of course I played with no error handling here for simplicity on my part. You may or may not want to add any yourself.