Loading ...
Sorry, an error occurred while loading the content.

MERGE/UPSERT with merely parameters

Expand Messages
  • Charles Carroll
    http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm shows how to use MERGE but not way I want. I want
    Message 1 of 1 , Oct 10, 2011
    • 0 Attachment
      http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm
      shows how to use MERGE but not way I want. I want to replace the SPROC
      below (and many others) with the MERGE command but my Sprocs use
      parameters not tables.

      Any syntax example of how to use MERGE when you have parameters not tables?

      USE [AutoStore_LogExt]
      GO
      /****** Object: StoredProcedure [dbo].[LogInfo_Upsert] Script
      Date: 10/10/2011 12:03:32 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      -- =============================================
      -- Author: Charles Carroll
      -- Create date: 4/29/2011
      -- =============================================
      ALTER PROCEDURE [dbo].[LogInfo_Upsert]
      @Category nvarchar(31),
      @InfoName nvarchar(31),
      @InfoValue nvarchar(254)
      AS
      BEGIN
      SET NOCOUNT ON;

      If exists(Select InfoValue from LogInfo WHERE (Category = @Category)
      AND InfoName=@InfoName AND (ltrim(rtrim(@InfoValue))
      =ltrim(rtrim(InfoValue))))
      begin
      UPDATE LogInfo
      SET Frequency = Frequency + 1, LastTime=GetDate()
      WHERE Category = @Category AND InfoName=@InfoName AND
      ltrim(rtrim(@InfoValue)) =ltrim(rtrim(InfoValue))
      end
      else
      begin
      Insert Into LogInfo
      ([Category],[InfoName],[InfoValue],[FirstTime],[LastTime],[Frequency])
      VALUES (@Category,@InfoName,@InfoValue,GetDate(),GetDate(),1)

      end
      END
    Your message has been successfully submitted and would be delivered to recipients shortly.