Translate

Friday, October 12, 2012

Insert Update with Merge

CREATE PROCEDURE usp_Widget_IU     
        @Widget dbo.udt_WidgetTableType READONLY
        ,@ReturnRecord bit = 1
AS
-- =============================================
-- Description: Insert/Update Widget
-- Parameters:
-- @Widget udt_WidgetTableType
-- @ReturnRecord bit [optional]
-- =============================================
BEGIN
    SET NOCOUNT ON;
    DECLARE @Return dbo.udt_WidgetTableType;
    MERGE dbo.[Widget] T
    USING @Widget A ON T.WidgetID = A.WidgetID
    WHEN MATCHED THEN  
        UPDATE SET T.[WidgetCode] =A.[WidgetCode]   
            ,T.[WidgetExpiration] =A.[WidgetExpiration]
    WHEN NOT MATCHED BY TARGET THEN  
        INSERT([WidgetCode],[WidgetExpiration])  
        VALUES(A.[WidgetCode],A.[WidgetExpiration])
    OUTPUT inserted.* INTO @Return;
    IF @ReturnRecord = CAST(1 AS BIT) SELECT * FROM @Return;
END
GO